Search code examples
oraclesql-loader

Bad file in Position based Control File- sqlldr

I have to use sqlldr to load from a flat file based on position.

Suppose I have a control file like;

LOAD DATA
INFILE 'sample.txt'
REPLACE INTO TABLE t1
(col1 POSITION(1:5),
 col2 POSITION(6:10),
 col3 POSITION(11:15)
)

Note : All 3 cols are VARCHAR2(5)

and a sample input file like;

ABCDE12345FGHIJ
AAA  BBBB CCCCC

Everything gets inserted as desired.

But if I have a line in input file like

AAAAABBBBBCCCCCDD

Its getting inserted to the table like

col1=AAAAA, col2=BBBBB, col3=CCCCC

(DD is ignored)

But what i want is to not insert this line at all and put such lines to badfile.How can I achieve this? I am new to sqlldr and all helps are appreciated!

Solution

  • If you change col3 from

     col3 POSITION(11:15)

    to

     col3 POSITION(11:16)

    then if there is a 16th character in a line the length of the col3 will be 6 and it will be rejected as too long for the column - you'll see ORA-012899 in the log file, and that record will go into the bad file.

    So your third example will be rejected, and the other two will still be inserted into your table.