sqlldr return codes - ex_warn

Question

I want to know if my file is loaded complete in the database.

if you check the return codes here you can see that 1 and 3 is a fail.

EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL  3

EX_WARN(return code 2) includes this cases:

All or some rows rejected EX_WARN

All or some rows discarded EX_WARN

Discontinued load EX_WARN

Now, the first and second is manageable.

For the third I had to search in the docs. If you read this you can see that "discontinued loads" include "fatal errors", "CTRL-C", and "space errors". In this cases I would probably get no records or some records rejected, EX_WARN return code, and the file incomplete loaded in database.

If there are no records rejected is simple: it was a discontinued load. I must exit with error. But when I have some record rejected I'm not sure that my file is completely loaded in database. (Some rows rejected is acceptable to me.) Am I right?

If yes, what the solution? How do I know if the entire table was loaded into DB?

Answer 1

You can have the situation where SQL Loader inserted (and committed) some rows out of a data file but failed to reach the end of that file (ie there could have been more records after the failure point which would have otherwise succeeded).

I'd opt for an external table over SQL Loader, using an INSERT INTO dest_table ... SELECT * FROM external_table. That would be an atomic operation and there's a (generally small) chance that it will fail if you have insufficient undo for rollback (since you are not using intermediate commits).

I'd also minimise the possibilities of rejections in the external table / SQL Loader layer by treating everything as generic text until it is loaded into the database. Then I'd apply the structure and use DML error logging to handle anything irregular. That way you have clear access to the rejected data and the reason for the rejection in the database.