Using sqlldr, can I ignore errors and load anyway?

[joeyb]

Specifically,

I have a table with a column defined as varchar2(300). My sqlldr input file may have more than 300 characters for that field. Is there a way to specify for sqlldr to stuff the data into the column (truncating whatever is over 300 characters)?

I would prefer this to sqlldr tossing out the entire row and not loading anything.

Thanks is advance!

Joe B.

[marist89]

Use substr(field_name,1,300) in your sqlldr controlfile.

[marist89]

See http://otn.oracle.com/docs/products/.../ch05.htm#6132 for details.

[opchandani]

Hi,
Make a normal control file and dont give any pos (positional) values for the fileds. Run the sqlldr command with the parameter ERROR= (a number grater then the number of rows u think wud have size > 300 ) (By default , ERROR=50).
Now, the loading will be complete and will reject all the rows having size>300. Now, even if the No. of these rows exceed 50, ur loading will not stop, and will completely load the rows as u require. If u provide the name of logfile while executing the sqlldr, u can actually see which rows were rejected and why.

[opchandani]

And, if u want to truncate and add the row, above 300, just give the pos vlaues for the field and run the sqlldr.