Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> sqlldr : How to upload records where all columns are null
Previous threads on this topic are here:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/34a0a2eff56c7348/90debfb3bb95cc91?lnk=st&q=%22discarded+all+columns+null%22+&rnum=1&hl=en#90debfb3bb95cc91 http://groups.google.com/group/comp.databases.oracle.tools/browse_thread/thread/dbb2f92771fefb01/c68021243b7bd611?lnk=st&q=%22discarded+all+columns+null%22+&rnum=2&hl=en#c68021243b7bd611 http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/257457169e8e8e12/63d99ee0dca06c56?lnk=st&q=%22discarded+all+columns+null%22+&rnum=4&hl=en#63d99ee0dca06c56 http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/a811982b03fe937b/fc5659dd7ba17eb9?lnk=st&q=%22discarded+all+columns+null%22+&rnum=3&hl=en#fc5659dd7ba17eb9... however I don't believe the question ever got answered.
Here is a case where I think it makes sense to load "empty" rows: I have a text file where certain lines need to be flagged if they are both preceded and followed by a blank line. My control file looks something like this:
LOAD DATA
INFILE 'ABCD.TXT'
APPEND
INTO TABLE raw_file_layouts
trailing nullcols
(line position (1:100) char " decode (:line , NULL,
'###', :line) "
,seq position (1) "LAYOUT_SEQ.NEXTVAL"
)
The strategy would be to look for cases where line is null for seq-1 and seq+1.
I've tried different variations of the above control file, without success. Oracle just refuses to load the blank lines, instead giving the message "Discarded - all columns null". In my opinion this is a bug, because in fact the columns are not null - the sequence number should not be null and neither should the "line" column.
The best thing I could come up with was to pre-process the file, adding a junk character at the beginning of each line, so that there wouldn't be any "blank" lines. But I was hoping there would be something easier, like a switch in SQL*Loader or something.
This is Oracle 9i, release 9.2.0.5.0. Received on Mon Feb 19 2007 - 20:22:58 CST