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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try giving 5     in every line  similar to line number 4

[oracle] Oracle sqlldr TRAILING NULLCOLS required, but why?

Examples related to oracle

Examples related to sql-loader