How to rollback when an error occurs while executi

Question

If while loading this file

$ cat employee.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

using the control file (say) sqlldr-add-new.ctl I came to know all the records are faulty so I want the previously loaded records in that table (those that were loaded yesterday) to be retained if today's had any error. How to handle this exception.

This is my sample ctl file

$ cat sqlldr-add-new.ctl
load data
infile '/home/ramesh/employee.txt'
into table employee
fields terminated by ","
( id, name, dept, salary )

Answer 1

You can't roll back from SQL*Loader, it commits automatically. This is mentioned in the errors parameter description:

On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.

And there's a section on interrupted loads.

You could attempt to load the data to a staging table, and if it is successful move the data into the real table (with delete/insert into .. select .., or with a partition swap if you have a large amount of data). Or you could use an external table and do the same thing, but you'd need a way to determine if the table had any discarded or rejected records.

Answer 2

try with ERRORS=0.

You could find all explanation here: http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67792/ch06.htm

ERRORS (errors to allow)

ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value of ERRORS parameter, SQL*Loader terminates the load. The default is 50. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.

On a single table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.

SQL*Loader maintains the consistency of records across all tables. Therefore, multi-table loads do not terminate immediately if errors exceed the error limit. When SQL*loader encounters the maximum number of errors for a multi-table load, it continues to load rows to ensure that valid rows previously loaded into tables are loaded into all tables and/or rejected rows filtered out of all tables.

In all cases, SQL*Loader writes erroneous records to the bad filz