Home » RDBMS Server » Server Utilities » CLOB inserted as varchar2 (10g )
CLOB inserted as varchar2 [message #479427] Sat, 16 October 2010 17:44 Go to next message
Messages: 20
Registered: October 2010
Junior Member

I ave a few fields in my flat file which might be a CLOB (not sure how the source is storing the data - need to check on that.) I am trying to load this data into my table column which is a varchar2(4000) . I am able to insert most of the data but few records are rejected because of
Field too long error....

While debugging the problm I manually copied the field from flatfile and inserted into my table - bingo it worked. (The field was not more than 1000 bytes - only a few lines of information )
My question:
When a field is not more than 1000 bytes why couldnt it get inserted as a varchar2?

Note :
I cannot make the table column as CLOB because the problem is not with just one column - I have 10 fields which have this problem . So its not advisable to have 10 CLOB fields in the table......

I have specified OPTIONS (BINDSIZE=256000,READSIZE=256000,ROWS=1)

Is this a known problem?
Re: CLOB inserted as varchar2 [message #479428 is a reply to message #479427] Sat, 16 October 2010 17:51 Go to previous messageGo to next message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

I can only speculate since you posted NOTHING of substance, but only your description of what you think you see.
If/when sqlldr does to correctly see or detect the end of the CLOB, your error would be thrown.
Re: CLOB inserted as varchar2 [message #479431 is a reply to message #479428] Sat, 16 October 2010 20:03 Go to previous message
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
Senior Member
You need to post your table structure, sample data, control file, and log file. A common error is not declaring a size for the field within the control file. If you do not declare a size, then SQL*Loader uses a default size of 255 and rejects anything larger than that, regardless of the size of the column in the table that it is trying to insert into.
Previous Topic: SQL LOADER
Next Topic: To_number conversion
Goto Forum:

Current Time: Mon May 20 12:46:13 CDT 2024