Can variables be transferred to the 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 the value of to batch_idto my control file when running SQL * Loader? For example, is it possible to specify a binding variable (rotation of question marks in :MY_AWESOME_BATCH_ID)?

Answer 1

- , .

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

Answer 2

, , :

• , SQLLoader , NULL , SQLPlus script , .
• script, , , batch_id.

Answer 3

If it's permissible for the BATCH_ID values ​​to be generated automatically, increasing at each load, than this worked for me. The 10-minute interval in the sample must be adjusted for a specific load - to be precise, the download must complete within the specified interval, and the next download should not start in less than the specified time.

The disadvantage is that it noticeably slows down on large volumes - the price at which the MAX aggregate on each row is calculated.

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
)