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: to - Release: 9.0.1 to 10.2
Information in this document applies to any platform.


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:

INFILE 'loaddata.dat'
 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

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.


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