Thanks for the question, rob.
Asked: August 04, 2009 - 6:24 pm UTC
Last updated: August 05, 2009 - 11:22 am UTC
Version: SQL*Loader: Release 9.2.0.1.0
Viewed 10K+ times! This question is 
You Asked
I have a data file with the following data into table:
1,2,3
4,5,6
7,8,9
The table looks like this:
SEQUENCE_NUM NUMBER(10),
NUM1 NUMBER(10),
NUM2 NUMBER(10),
NUM3 NUMBER(10)
I have a control file like this:
OPTIONS(ERRORS=0) LOAD DATA
TRUNCATE
INTO TABLE mytable
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(
seq "SEQUENCE_NAME.NEXTVAL"
, num1
, num2
, num3
)
My table would get something like this:
2 3 null seq1
5 6 null seq2
8 9 null seq3
which is wrong.
I want is to look like this:
1 2 3 seq1
4 5 6 seq2
7 8 9 seq3
If my data file looks like this:
,1,2,3
,4,5,6
,7,8,9
That would solve the problem.
But I don't want to add extra column in the
data file. I have 5 diiferent columns that are created by other
means(not from the data file) and I don't want users to send extra
','.
Regards
and Tom said...
put seq LAST
ops$tkyte%ORA10GR2> create sequence sequence_name;
Sequence created.
ops$tkyte%ORA10GR2> create table mytable (sequence_num number, num1 number, num2 number, num3 number );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !cat test.ctl
OPTIONS(ERRORS=0)
LOAD DATA
infile *
TRUNCATE
INTO TABLE mytable
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(
num1,
num2,
num3,
sequence_num "SEQUENCE_NAME.NEXTVAL"
)
begindata
1,2,3
4,5,6
7,8,9
ops$tkyte%ORA10GR2> !sqlldr / test
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Aug 5 10:57:52 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 3
ops$tkyte%ORA10GR2> select * from mytable;
SEQUENCE_NUM NUM1 NUM2 NUM3
------------ ---------- ---------- ----------
1 1 2 3
2 4 5 6
3 7 8 9