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)?

Answer 1

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

• If there's only going to be one process running SQL*Loader at a time, use nulls or a fixed value and then run a SQL*Plus 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.

Answer 2

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)
)