I am using SEQUENCE keyword in SQL Loader control file to generate primary keys. But for a special scenario I would like to use Oracle sequence in the control file. The Oracle documentation for SQL Loader doesn't mentioned anything about it. does SQL Loader support it?
Add a comment
|
3 Answers
I have managed to load without using the dummy by the switching the sequence to be the last column as in :
LOAD DATA
INFILE 'data.csv'
APPEND INTO TABLE my_data
FIELDS TERMINATED BY ','
(
name char,
ID "MY_SEQUENCE.NEXTVAL"
)
and data.csv would be like:
"dave"
"carol"
"tim"
"sue"
1 Comment
broeni
You have to add
TRAILING NULLCOLS to the control file.I have successfully used a sequence from my Oracle 10g database to populate a primary key field during an sqlldr run:
Here is my data.ctl:
LOAD DATA
INFILE 'data.csv'
APPEND INTO TABLE my_data
FIELDS TERMINATED BY ','
(
ID "MY_SEQUENCE.NEXTVAL",
name char
)
and my data.csv:
-1, "dave"
-1, "carol"
-1, "tim"
-1, "sue"
For some reason you have to put a dummy value in the CSV file even though you'd figure that sqlldr would just figure out that you wanted to use a sequence.
