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!?!

Bashuk

Because Larry said so, that's why. That's the intended behavior. – Equatorial

It's ok that it's intended, I just want to understand the mechanics of it. – Bashuk

Answer 1

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.

Veinlet

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.... – Bashuk

Haven't been able to validate this, but it's not fair to keep your points waiting.... – Bashuk

Answer 2

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

Flour

Answer 3

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

Geesey

Answer 4

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

Vastitude

Answer 5

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

Madox