Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

problem with loading long data using sqldr

Faris Hussain
Occasional Visitor

problem with loading long data using sqldr

Hi,
I am trying to load some data in to table which contains a long datatype. The data i am trying to load contains a column that is pretty large, some up to 8000 characters which should go into the long datatype column.

when i use the sqlldr it does not give any errors, but only loads around 100 characters to this column .


here is my control file:
LOAD DATA
INFILE 'entity3.dat'
BADFILE 'entity3.bad'
TRUNCATE INTO TABLE SONEDBA.ENTITY3
FIELDS TERMINATED BY "@"
TRAILING NULLCOLS
(ENTITY_KEY, ID, ENTITY_NAME, BITMAP,PRIMARY_KEY_COLUMN, LOCK_VERSION, SQL_STATEMENT CHAR (20000))

entity3.dat file, which contains the data holds a column which is around 8000 charcaters.

I checked the control file but no errors.
The long datatype is SQL_STATEMENT column.

Thanks for your help inadvance..


DBA
5 REPLIES
harry d brown jr
Honored Contributor

Re: problem with loading long data using sqldr

Faris,

have you looked into either writing a "c" or "perl" program to load your data? I think you are running into a command line IO length issue.

live free or die
harry
Live Free or Die
A. Clay Stephenson
Acclaimed Contributor

Re: problem with loading long data using sqldr

I assume that there are no '@'s within the SQL statements. You might try adding the 'optionally enclosed by '"' clause.

e.g.
FIELDS TERMINATED BY '@' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

If it ain't broke, I can fix that.
A. Clay Stephenson
Acclaimed Contributor

Re: problem with loading long data using sqldr

Hi Again:

I also suggest that you search Oracle's MetaLink. There are a number of posting with problems very similar to yours.
If it ain't broke, I can fix that.
Faris Hussain
Occasional Visitor

Re: problem with loading long data using sqldr

Well,, i did try the optinally clase but did not work.
also, i set the bindsize to 20000 still have the same problem
DBA
Andreas D. Skjervold
Honored Contributor

Re: problem with loading long data using sqldr

Hi

No errors you say?? What about the .log file. Can you post it here?

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!