Can variables be passed to a SQL*Loader control file?

Question

Suppose you have a table:

CREATE TABLE Customer
(
  batch_id         NUMBER,
  customer_name    VARCHAR2(20),
  customer_address VARCHAR2(100)
)

And suppose you have a control file to populate this table:

LOAD DATA INFILE 'customers.dat'
REPLACE

INTO TABLE Customer
(
  batch_id         ??????,
  customer_name    POSITION(001:020),
  customer_address POSITION(021:120)
)

Is it possible to pass a value for batch_id to my control file when I run SQL*Loader? For example, is it possible to specify a bind variable (turning the question marks into :MY_AWESOME_BATCH_ID)?

score 8 · Accepted Answer · answered Sep 21 '10 at 13:19

8

A relatively easy way to archive that is to create a stored function that returns the batch number and use it in the loader file.

create or replace function getBatchNumber return number as
begin
  return 815;
end;
/

LOAD DATA INFILE 'customers.dat'
REPLACE

INTO TABLE Customer
(
  batch_id         "getBatchNumber",
  customer_name    POSITION(001:020),
  customer_address POSITION(021:120)
)

answered Sep 21 '10 at 13:19

Erich Kitzmueller

36,381
5
80
102

Nice. If you want the function to do a new batch for each run of Loader, put it in a package and make it return a package variable that will be initialized (for example, to a sequence) on the first call. – Jim Hudson Sep 22 '10 at 13:21
You might also decide to recreate the function on-the-fly in a script, before starting sqlloader. – Erich Kitzmueller Sep 22 '10 at 14:00

score 3 · Answer 2 · answered Sep 21 '10 at 13:09

Not easily, if I remember right, but here are a couple of alternatives:

If there's only going to be one process running SQLLoader at a time, use nulls or a fixed value and then run a SQLPlus script as part of the process afterwards to do the update to a sequence value.
Call a script which will grab the next sequence value for your batch ID and then spool out the control file, including the batch_id constant.

Vitali Tchalov · Answer 3 · 2012-05-18T21:34:11.577

1

If it's acceptable to have BATCH_ID values generated automatically by incrementing on each load, than this worked for me. The 10 minutes interval in the sample would need to be adjusted to the specific load - to be accurate, the loading must complete within the specified interval and the next loading must not be started in less than time specified.

A drawback is that it slows down noticeably on large volumes - that's the price running the MAX aggregate on every line.

LOAD DATA
...
INTO TABLE XYZ 
(
...
BATCH_ID expression "(select nvl(max(batch_id) + 1, 1) from extra_instruments_party_to where create_date < (sysdate - interval '10' minute))",
CREATE_DATE SYSDATE
)

edited May 18 '12 at 21:34

answered May 18 '12 at 21:17

Vitali Tchalov

722
6
5

Interesting approach. Could also just create a sequence, use it in the control file, and delete it after. – Glenn Jun 21 '12 at 00:56
Unless I'm missing the idea I don't think the sequence would work for this purpose - we need an ID for a group of records, not for each individual record; the sequence would increment in each insert. – Vitali Tchalov Jun 22 '12 at 16:19

Can variables be passed to a SQL*Loader control file?

3 Answers3