Re: ampersand in sql loader data file

In article <xLg%4.3852$v_.406026_at_nntp2.onemain.com>,   "Noah Couture" <noah_at_coutureconsulting.com> wrote:
> Hello,
>
> I'm attempting to load a very large (538 million rows) datafile into a
 table
> using sqlloader. I only get about 450 rows into it and then I
 encounter a
> row that contains the much reviled '&' character. I've seen that you
 can
> turn scanning off in sqlplus to avoid this hyperactive interpolation
> feature, but how is this done within the context of sqlldr?
>
> thanks
> noah

Noah,

There is a log generated every time you run sqlldr. In this log you will find the number of rows loaded, the number of rows that were not loaded due to data errors and the data error for each row not loaded. A .bad file is also generated, usually named after the data file, that contains all rows rejected by sqlldr. Check this log file for errors -- it could be anything causing this to occur. My hunch is a data format error -- a value too large for the column, a text value in a number column, incorrect date format, etc. Once you discover the reasons for the data load problems you can correct the data or the table definition and continue on. Also, the default number of errors that sqlldr will accept and continue processing is 50. This can be reset on the command line using the 'errors=<number>" syntax:

sqlldr abu/dhabi my.ctl errors=600000000

This will cause sqlldr to process data until 600 million errors have been encountered. Since you are processing about 540 million rows this should allow all records to be processed and will generate, most likely, a very large .bad file. You can then check the log and the .bad file and make any adjustments so the data will load properly.

I hope this helps.

David Fitzjarrell
Oracle DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 07 2000 - 00:00:00 CDT