sqlldr - skip lines with null values

0
votes

I have a requirement to load data from delimited file to Oracle 12c.

My Ctl file looks like below :

OPTIONS (skip=2,rows=1000, bindsize=100000, readsize=100000, discardmax=1, silent=header,feedback)
load data
CHARACTERSET UTF8
insert into table XYZ
WHEN FIELD2 <> ''
fields terminated by '^' optionally enclosed by ','
trailing nullcols
(
FIELD1 filler,
FIELD2,
FIELD3,
MODIFIED_DATE "SYSDATE"
)

In my data file, i am expecting atleast one row with NULL for FIELD2 & FIELD3.

Assume my data file is :

ABC^DEF^GHI

ABC^^

My second record gets discarded as intended , with the below message :

1 Row successfully loaded.

0 Rows not loaded due to data errors.

1 Row not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

However, sqlldr exit code remains 2 , causing my script to fail. Is there a way to load the valid records (rows with FIELD2 as not null) & gracefully success exit ?

Any help is greatly appreciated.

Thanks.

1
sql-loader
Assume my data file is : ABC^DEF^GHI ABC^^ - pats4u

1 Answers

0
votes

Any WHEN condition that fails will cause sqlldr to exit non-zero, as you have discovered.

You will need to either:

  • Pre-process the file and remove those lines with NULL fields before loading

  • Load all records into your table then delete those with the NULL
    second column

  • Load all records into a staging table then select the rows you want
    into the main table

  • Use an external table and select the rows you want into the main
    table

Each method has its pros and cons so you'll have to do some studying and see which will work best for your situation.