Load blank or null lines with SQL*Loader
844952Mar 4 2011 — edited Mar 4 2011I 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