Using SQL*Loader WIth Parameter NO_INDEX_ERRORS=TRUE Causes An Inconsistent Table State

(Doc ID 1396694.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

You started SQL*Loader in direct path mode with parameter NO_INDEX_ERRORS=TRUE and observed an inconsistent table state after the load. The next test case demonstrates this:

-- create the environment
connect test/test

create table t (x number, constraint t_pk primary key(x));

-- SQL*Loader control file ldr.ctl
options (direct=true, errors=0, no_index_errors=true)
load data
infile *
truncate
into table t
fields terminated by ','
trailing nullcols
(
   x
)
begindata
1
x

-- start SQL*Loader
#> sqlldr test/test control=ldr.ctl

This terminates with:

Load completed - logical record count 2.

The log file contains:

Record 2: Rejected - Error on table T, column X.
ORA-01722: invalid number

Specify SKIP=1 when continuing the load.
The following index(es) on table T were processed:
index TEST.T_PK loaded successfully with 1 keys

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table T:
1 Row 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.

Then:

SQL> select count (*) from t;

COUNT(*)
----------
         0

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T_PK) violated

Note: This occurs *only* when ERRORS=<n> is specified. It seems, when the count of allowed errors is exceeded, the index remains loaded but not the table. This explains count 0 in table but ORA-1 from index when an already existing value is again inserted.

If you take out ERRORS=0 from control file above, then the behavior is the expected one:

- the count from table shows 1 row
- the index is loaded with 1 key

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms