Oracle sqlldr TRAILING NULLCOLS required, but why?

Question

I have an abstruse sqlldr problem that's bothering me. My control file looks something like this:

load data
infile 'txgen.dat'
into table TRANSACTION_NEW
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( A,
  B,
  C,
  D,
  ID "ID_SEQ.NEXTVAL"
)

Data is something like this:

a,b,c,
a,b,,d
a,b,,
a,b,c,d

If I don't put the TRAILING NULLCOLS in, I get the "column not found before end of logical record" error. But although some of the columns are null, the commas are all there, so I don't see a reason for sqlldr to misinterpret the input file, and not get to the end where it generates the ID from the database sequence.

This syntax has worked before with no null columns - why does a null column cause sqlldr to not reach the generated column?

I've got it working, I just want to understand WHY!?!

Because Larry said so, that's why. That's the intended behavior. — Adam Musch, Oct 12 '10 at 17:25
It's ok that it's intended, I just want to understand the mechanics of it. — orbfish, Oct 15 '10 at 19:50

DCookie · Accepted Answer · 2010-10-15T22:41:47.230

You have defined 5 fields in your control file. Your fields are terminated by a comma, so you need 5 commas in each record for the 5 fields unless TRAILING NULLCOLS is specified, even though you are loading the ID field with a sequence value via the SQL String.

RE: Comment by OP

That's not my experience with a brief test. With the following control file:

load data
infile *
into table T_new
fields terminated by "," optionally enclosed by '"'
( A,
  B,
  C,
  D,
  ID "ID_SEQ.NEXTVAL"
)
BEGINDATA
1,1,,,
2,2,2,,
3,3,3,3,
4,4,4,4,,
,,,,,

Produced the following output:

Table T_NEW, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A                                   FIRST     *   ,  O(") CHARACTER            
B                                    NEXT     *   ,  O(") CHARACTER            
C                                    NEXT     *   ,  O(") CHARACTER            
D                                    NEXT     *   ,  O(") CHARACTER            
ID                                   NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "ID_SEQ.NEXTVAL"

Record 1: Rejected - Error on table T_NEW, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table T_NEW, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table T_NEW, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Discarded - all columns null.

Table T_NEW:
  1 Row successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  1 Row not loaded because all fields were null.

Note that the only row that loaded correctly had 5 commas. Even the 3rd row, with all data values present except ID, the data does not load. Unless I'm missing something...

I'm using 10gR2.

That is not so, because if the data always has 4 fields (but not 5 commas, or even 4), you do not need the TRAILING NULLCOLS. Though you're onto something there, it almost makes sense.... — orbfish, Oct 15 '10 at 19:47
Haven't been able to validate this, but it's not fair to keep your points waiting.... — orbfish, Nov 07 '11 at 21:42

Chris · Answer 2 · 2015-12-09T18:32:42.133

4

The problem here is that you have defined ID as a field in your data file when what you want is to just use an expression without any data from the data file. You can fix this by defining ID as an expression (or a sequence in this case)

ID EXPRESSION "ID_SEQ.nextval"

or

ID SEQUENCE(count)

See: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1008234 for all options

edited Dec 09 '15 at 18:32

answered Jun 17 '14 at 15:26

Chris

691
4
12

score 3 · Answer 3 · answered May 30 '13 at 20:52

Last column in your input file must have some data in it (be it space or char, but not null). I guess, 1st record contains null after last ',' which sqlldr won't recognize unless specifically asked to recognize nulls using TRAILING NULLCOLS option. Alternatively, if you don't want to use TRAILING NULLCOLS, you will have to take care of those NULLs before you pass the file to sqlldr. Hope this helps

score 0 · Answer 4 · answered May 16 '13 at 04:21

0

Try giving 5 ',' in every line, similar to line number 4.

answered May 16 '13 at 04:21

ram

1

score 0 · Answer 5 · answered Mar 21 '17 at 07:03

I had similar issue when I had plenty of extra records in csv file with empty values. If I open csv file in notepad then empty lines looks like this: ,,,, ,,,, ,,,, ,,,,

You can not see those if open in Excel. Please check in Notepad and delete those records

Oracle sqlldr TRAILING NULLCOLS required, but why?

5 Answers5

Linked