Null Value Inserted In Primary Key Column When Loading Data Via Sql*Loader Using Direct Path Load Mode (Doc ID 453346.1)

Last updated on OCTOBER 06, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 10.2.0.1 - Release: 9.0.1 to 10.2
Information in this document applies to any platform.

Symptoms

Null values are being inserted into Primary key column when loading data via Sql*loader in Direct Path Load mode.


--Steps to reproduce:

 a) Create a table say emp in a schema

 create table emp
 (accno number primary key);

b) Create a sequence in the same schema
create sequence emp_seq;

c) Create a control file - load.ctl with contents as:

 LOAD DATA 
INFILE 'loaddata.dat'
 append
 INTO    TABLE   emp
 WHEN (1:2) = 'KB'
 (accno               "emp_seq.nextval" 
)
 into table emp
 WHEN (1:2) = 'BB'
 (accno               "emp_seq.currval"
 )

d) Create a datafile called loaddata.dat with contents in the same directory
 KB24062002BRUTTOSUMMENSATZ -20021-DS65

e) Use the following command
 sqlldr <username>/<password> control=load.ctl log=load.log direct=yes          
                     
You would find that first command puts a null value into the table where as the column is defined as primary 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