sqlldr primary key column with null values
It's nice to be here i mean in my oracle blog again after a long time period :)
I will tell you how can you populate primary key column with getting data from sequence when you insert data with sqlloader.
for instance think about table structure below
create table T_SERVICE_AVAILABILITY_ID
(
ID NUMBER,
HOSTNAME VARCHAR2(50),
VRFNAME VARCHAR2(50),
VRFSTATUS VARCHAR2(5),
DATEX DATE
)
and you want to load data such as
HOST01,SERVICE1,up,26/01/10 09:48:40
HOST01,SERVICE1,up,26/01/10 09:48:40
HOST01,SERVICE2,down,26/01/10 09:48:41
as you see here we have an id column on table but not in data
so we must create appropriate trigger and sequence pair to get pk values from trigger
I'm skipping this step, it's pretty easy
then our control.ctl file should be like below format
LOAD DATA
BADFILE 'import.bad'
APPEND
INTO TABLE T_SERVICE_AVAILABILITY_ID
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
HOSTNAME CHAR NULLIF HOSTNAME = BLANKS,
VRFNAME CHAR NULLIF VRFNAME = BLANKS,
VRFSTATUS CHAR NULLIF VRFSTATUS = BLANKS,
DATEX DATE "DD/MM/YY,HH24:MI:SS" NULLIF DATEX = BLANKS
)
Important thing is here TRAILING NULLCOLS parameter to treat oracle to pk field as null
in other words by sending null for id column
I will tell you how can you populate primary key column with getting data from sequence when you insert data with sqlloader.
for instance think about table structure below
create table T_SERVICE_AVAILABILITY_ID
(
ID NUMBER,
HOSTNAME VARCHAR2(50),
VRFNAME VARCHAR2(50),
VRFSTATUS VARCHAR2(5),
DATEX DATE
)
and you want to load data such as
HOST01,SERVICE1,up,26/01/10 09:48:40
HOST01,SERVICE1,up,26/01/10 09:48:40
HOST01,SERVICE2,down,26/01/10 09:48:41
as you see here we have an id column on table but not in data
so we must create appropriate trigger and sequence pair to get pk values from trigger
I'm skipping this step, it's pretty easy
then our control.ctl file should be like below format
LOAD DATA
BADFILE 'import.bad'
APPEND
INTO TABLE T_SERVICE_AVAILABILITY_ID
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
HOSTNAME CHAR NULLIF HOSTNAME = BLANKS,
VRFNAME CHAR NULLIF VRFNAME = BLANKS,
VRFSTATUS CHAR NULLIF VRFSTATUS = BLANKS,
DATEX DATE "DD/MM/YY,HH24:MI:SS" NULLIF DATEX = BLANKS
)
Important thing is here TRAILING NULLCOLS parameter to treat oracle to pk field as null
in other words by sending null for id column
Comments