OraFAQ Forum: Server Utilities » Problem using SUBSTR

Home » RDBMS Server » Server Utilities » Problem using SUBSTR

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem using SUBSTR [message #165495]

Thu, 30 March 2006 11:17

hibyte
Messages: 11
Registered: March 2006

Junior Member

I'm trying to use SUBSTR within SQL*Loader (9.2.0.5/Solaris 64) as:

custname char(50) "SUBSTR(:custname, 1, 50)",

Flatfile contains custname field to be more than 100 char sometimes .

While running the load, it still complains that:
Record 1827: Rejected - Error on table TEST_200602, column CUSTNAME.
Field in data file exceeds maximum length

The custname field in datafile contains special char. such as "&", ",", "-" etc. and file is pipe(|) separated.

Any idea how to solve this problem.

Thanks,
Has

Report message to a moderator

Re: Problem using SUBSTR [message #165507 is a reply to message #165495]

Thu, 30 March 2006 13:11

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

What are the datatype and length of the column in the table that you are trying to load the data into? What happens if you leave the "char(50)" out of the control file?

Report message to a moderator

Re: Problem using SUBSTR [message #165508 is a reply to message #165507]

Thu, 30 March 2006 13:21

hibyte
Messages: 11
Registered: March 2006

Junior Member

Data type for custname column is varchar2(50).
I've not tried leaving char(50) out of control file. Will it help?

The file has custname field mostly < 50 char but sometimes about 100 char.

Thanks,Has

Report message to a moderator