Load data via SQL * Loader in IDENTITY column in Oracle 12c

1
votes

I need to load data using SQL* Loader into Oracle table with GENERATE ALWAYS AS IDENTITY

CREATE TABLE tbl_identity
(
  col1 NUMBER(10,0) GENERATED ALWAYS AS IDENTITY(START WITH 7 INCREMENT BY 1)    NOT NULL,
  col2 VARCHAR2(20)
);

But when I try to load data in this table, I get an error 

ORA-32795: cannot insert into a generated always identity column

Maybe there are any option in SQLLDR which skip IDENTITY for a while?

Or maybe there are any ideas how I can load data into GENERATED ALWAYS IDENTITY column using SQLLDR?

SQLLDR command

sqlldr userid=user/passw@TNS control=tbl_identity.ctl log=tbl_identity.log rows=1000 readsize=65535 bindsize=65535

CTL-file

OPTIONS(direct=false)
LOAD DATA
INFILE 'tbl_identity.txt'
INTO TABLE tbl_identity
INSERT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(col1,
 col2)
1
sqloracleidentitysql-loader
ignore that column and load the others. can you show the sql loader script? - Vamsi Prabhala
But if I have something like this 1 'one' 2 'two' 4 'four' And If I ignore 1st column, it will load me incorrect data Here is SQLLDR command sqlldr userid=user/passw@TNS control=tbl_identity.ctl log=tbl_identity.log rows=1000 readsize=65535 bindsize=65535 - Walentyna Juszkiewicz
I think you should post the content of tbl_identity.ctl - etsa
OPTIONS(direct=false) LOAD DATA INFILE 'tbl_identity.txt' INTO TABLE tbl_identity INSERT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (col1, col2) - Walentyna Juszkiewicz

1 Answers

1
votes

Change your DDL from GENERATED ALWAYS to GENERATED BY DEFAULT