How to rollback when an error occurs while executing sql loader command?

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 )

Alex Poole · Accepted Answer · 2016-11-15 16:03:15Z

4

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.

edited Nov 15, 2016 at 16:03

answered May 13, 2014 at 16:36

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Boneist Over a year ago

Updated link for the errors parameter description

eliatou · Accepted Answer · 2014-05-13 16:34:29Z

1

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

answered May 13, 2014 at 16:34

eliatou

7445 silver badges15 bronze badges

1 Comment

Alex Poole Over a year ago

"Any data inserted up that point, however, is committed". And any existing data in the table will already be gone, whether remove or truncate is used, unfortunately.

Collectives™ on Stack Overflow

How to rollback when an error occurs while executing sql loader command?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related