Is it possible to load csv file with sqlldr and use sequence at the same time?
Say for example I would like to use command
sqlldr id/pass@'ip:1521/ORCL' data=path\csv_test.csv
control=path\control.ctl log=path\log.log bad=path\bad.csv
to load csv file into database but at the same time use sequence to create an extra column that increments for every csv file insert (so every bulk insert of a csv file)
Sure there's an option; it is called a sequence. More info about it in Field List Reference documentation.
Here's an example.
Data will be loaded into the TEST table:
SQL> create table test
2 (id number,
3 ename varchar2(20)
4 );
Table created.
SQL>
A sequence will be used for the ID column. Control file looks like this:
load data
infile *
replace
into table test
(
id sequence,
ename char terminated by whitespace
)
begindata
Little
Foot
Stack
Over
Flow
Loading session:
M:\a1_maknuto>sqlldr scott/tiger@orcl control=test21.ctl
SQL*Loader: Release 11.2.0.2.0 - Production on Pon Vel 19 07:20:29 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Results:
SQL> select * From test;
ID ENAME
---------- --------------------
1 Little
2 Foot
3 Stack
4 Over
5 Flow
SQL>
[EDIT: aha, all rows should share the same "sequence"]
OK then, try something like this (note expression used for the ID column):
load data
infile *
append
into table test
(
id expression "userenv('sessionid')",
ename char(30) terminated by whitespace
)
begindata
Little
Foot
Stack
Over
Flow
A few loading sessions:
SQL> $sqlldr scott/tiger@orcl control=test21.ctl
SQL*Loader: Release 11.2.0.2.0 - Production on Pon Vel 19 08:13:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
Commit point reached - logical record count 5
SQL> select * From test;
ID ENAME
---------- --------------------
4530297 Little
4530297 Foot
4530297 Stack
4530297 Over
4530297 Flow
SQL> $sqlldr scott/tiger@orcl control=test21.ctl
SQL*Loader: Release 11.2.0.2.0 - Production on Pon Vel 19 08:13:30 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
Commit point reached - logical record count 5
SQL> select * From test;
ID ENAME
---------- --------------------
4530297 Little
4530297 Foot
4530297 Stack
4530297 Over
4530297 Flow
4530301 Little
4530301 Foot
4530301 Stack
4530301 Over
4530301 Flow
10 rows selected.
SQL>
Alternatively, you could use a sequence (an Oracle object). That's a little bit more "complex" (you'd need a function too) but - if you need it, I can create an example. Say so.
