CODEWITHSUNDEEP
oracle-databasesql-loader
forJ
forJ

Reputation: 4617

Using sqlldr to load csv file with sequence

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)

Upvotes: 0

Views: 5423

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

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.

Upvotes: 2

Related Questions