Sql loader: interpreting errors

[DoraC]

Hi,

I'm using SqlLoader to load some data, and - while records are rejecting - the log file isn't telling me what is wrong with the records (merely that they are problematic). Is there some "switch" I can turn on in order to get meaningful information, or am I forced to investigate the data, field by field?

Thanks!
Dora

 

[revnice]

Dude:

I don't know of a switch but if you print a desc of the table it's usually pretty easy to spot the problem.

* Column length exceeded
* Incorrect data type
* Constraint violation

..are the commonest causes.

rev

 

[GAllan60]

Hi,

Do some of the records load and others not or do all the records fail to load? Is there any data in the table prior to you import?

I had a look through several of my old import reports and there was a list of errors after the table definition section.

If you are having trouble importing the data two options for checking the import are:

1. Look in the *.bad file. This will contain the records that could not be loaded. Try to insert one of these records by copying the data and changing it into a SQL insert statement. If you try to insert this record you may be able to work out where the error is.

2. Load the data into a new table where all the fields are large varchar2's after loading the data check using LENGTH for text and the MAX for numbers, check this against the original table. After checking the data can be inserted into the original table using a sub query.

If the data will not load into this table you may have a problem with the use of the delimiter or end of line character in the text.

Regards

Graham