Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> sqlldr : How to upload records where all columns are null

sqlldr : How to upload records where all columns are null

From: <tw12345_at_mailinator.com>
Date: 19 Feb 2007 18:22:58 -0800
Message-ID: <1171938178.460488.87520@q2g2000cwa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US