Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Load blank or null lines with SQL*Loader

844952Mar 4 2011 — edited Mar 4 2011
I want to retain blank (or null) lines present in the input file, which SQL*Loader retains. My control file looks like this:

LOAD DATA
INFILE 'c:\sdfload\compounds.sdf'
BADFILE 'c:\sdfload\compounds.log'
INTO TABLE sdfile
( rid "sdf_seq.nextval",
sdfrec POSITION (1:600) NULLIF rid=BLANKS
)

The first five lines of input look like (the blank lines look to be end-of-line or tab characters).

Marvin 02171107552D

38 43 0 0 0 0 999 V2000
1.3379 -2.6605 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

The output looks like this (first and third records excluded)

RID SDFREC
------ ------------------------------------------------------------------------
1 Marvin 02171107552D
2 38 43 0 0 0 0 999 V2000
3 1.3379 -2.6605 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0


How do I get output that looks like this?

RID SDFREC
------ ------------------------------------------------------------------------
1
2 Marvin 02171107552D
3
4 38 43 0 0 0 0 999 V2000
5 1.3379 -2.6605 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0

Any help or suggestions will be greatly appreciated!

-Mike
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2011
Added on Mar 4 2011
2 comments
411 views