My Oracle Support Banner

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 FEBRUARY 03, 2019

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.