ampersand in sql loader data file
Noah Couture
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
Thomas J. Kyte
& has no effect on sqlldr. & is a sqlplus'ism -- sqlldr does not
do "&", it is not the problem.
Whats the error you are getting?
--
Thomas Kyte (tk...@us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
ddf...@my-deja.com
"Noah Couture" <no...@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
Noah Couture
% sqlldr ...
...
...
Commit point reached - logical record count 167
ORA-22990: LOB locators cannot span transactions
Segmentation Fault (core dumped)
It dumps core before writing anything to the log or bad file. I was
assuming that the & was the LOB locator, but I guess thats wrong. I have no
idea what a lob locator is. I do however have three CLOB columns, but I was
treating them as char columns in the ctl file and including their contents
inline as string just like i'm doing for varchar2 columns. I'm guessing I
need to do something special for these columns. If I try to do the same
thing using direct, it seems to work great but I get an internal error when
I try to select any of the clob columns.
Does this make more sense?
thanks
noah
"Thomas J. Kyte" <tk...@us.oracle.com> wrote in message
news:8hlhoc$tfg$1...@nnrp1.deja.com...
> In article <xLg%4.3852$v_.4...@nntp2.onemain.com>,
> "Noah Couture" <no...@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
> >
> >
>
> & has no effect on sqlldr. & is a sqlplus'ism -- sqlldr does not
> do "&", it is not the problem.
>
> Whats the error you are getting?
>
>
> --
> Thomas Kyte (tk...@us.oracle.com) Oracle Service Industries
> Howtos and such: http://osi.oracle.com/~tkyte/index.html
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
>