SQL Loader and rollback

[petersJazz]

hi,

is there a way to get SQL Loader to rollback if it finds an error while loading a file.

This is part of the log I get:

Record 3: Rejected - Error on table CRPDTA.F03B13Z1.
ORA-00001: unique constraint (CRPDTA.F03B13Z1_1) violated

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table CRPDTA.F03B13Z1:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
[/color green]
and I use the command:

sqlldr errors=0 rows=999 discardmax=1 control=$WORKDIR/xyz.tmp userid=$ORAUSER/$ORAPASS@$ORADB data=$IMPDIR/xyz.dat bad=$WORKDIR/xyz.bad log=$WORKDIR/xyz.log >$WORKDIR/xyz.out 2>&1
[/color green]

regards
Peter

 

[oradba101]

Hi, Peter

SQL*Loader does not have a rollback feature. However, you can get it to start from the last record loaded. But apparently there is a difference between doing it with the conventional vs. direct path. Can you tell me which you are using?

Regards,

William Chadbourne
Oracle DBA

 

[petersJazz]

hi,

sorry, I dont know the difference between conventional and direct path.

So if I need it to be loaded all or nothing I need to do some logics around it like:

run sqlldr
check for error
if error then
delete records

But this is a standard system and the inbox table is used by several systems so its difficult to know which records to delete.

Maybee the best thing is to us another temporary table:

run sqlldr into temp table
run stored procedure to load inbox table
signal in stored procedure if there is errors

My problem is that the inbox table has some unique index and that it contains a lot of row from different systems.

regards
Peter