data load using sql loader giving bad records but Batch job succeeding

0
votes

data load using sql loader giving bad records but oracle job succeeding. I want to fail the job if it creates even a single record rejects and creates bad file. Please help me with this.

1
sqloraclesql-loader
What "oracle job" are we talking about? Do you have a dba_jobs job? A dbms_scheduler job? A cron job running at the operating system level? Something else? Perhaps you just want to specify errors=0 in your command line. - Justin Cave
We have 3rd party job scheduler which calls a batch script, inside batch script we use SQL loader job to run. - San

1 Answers

0
votes

SQL*Loader, by default, allows 50 errors before it will cause the loader job to fail:

https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm

errors -- number of errors to allow            (Default 50)

Within your control file or on the command line when you call SQL*Loader, if you change the errors parameter, you can alter this to whatever you want -- in your case, it sounds like you want zero.

Here is an example:

  $SQLDIR/sqlldr /@server.whatevz.com \
    control=/apps/loader/table1.ctl \
    log=/apps/loader/table1.log \
    bad=/apps/loader/table1.err \
    rows=200000 \
    errors=0