Accessing data filename from within SQL*Loader control file

How do I access the input data file name from within SQL*Loader control file so that I can insert it into the table along with data from the input file?

Let's say for example I have the following control file:

LOAD DATA

APPEND
INTO TABLE STG_AM02_BA_RAW
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR
)

I want to do something like:

LOAD DATA

APPEND
INTO TABLE STG_AM02_BA_RAW
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR, 
        INPUTFILE                       INPUTFILENAME()CHAR
)

Assume that I don't have access nor permission to edit the shell script that will invoke SQL*Loader with this control file.

Solution

As of 11g, it isn't possible to access the filename directly from the SQL*Loader control file.

You basically have to handle it from your scripting environment.

If you're not able to modify the loading script, perhaps you could add a header record to the datafile?

It looks like you have a record type field in position 1:2 - can you modify the datafile creation to include a filename record type?

For example, a "FN" data type:

FN                ...        inputfile.txt
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY
DT     12345678XXX...XXXXXYYYYYYYYYYYYYYYY

Your load script could then change to:

LOAD DATA

APPEND
INTO TABLE STG_AM02_BA_RAW
WHEN (1:2) = 'FN'
(
        INPUTFILE                       POSITION(1:92)CHAR
)
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR
)

All depends if you can update the data file...

For example,

echo "FNinputfile.txt" > header.txt
cat header.txt inputfile.txt > newinputfile.txt

If you need to reference the filename against each data row, you can load the data into multiple staging tables:

LOAD DATA
TRUNCATE INTO TABLE STAGE_FILENAME
WHEN (1:2) = 'FN'
(
        INPUTFILE                       POSITION(1:92)CHAR
)
TRUNCATE INTO TABLE STAGE_DATA
WHEN (1:2) = 'DT'
(
        SUBSCRIBER_NO                   POSITION(11:18)CHAR, 
        ACCOUNT_NO                      POSITION(19:32)CHAR, 
        SUBSCRIBER_NAME                 POSITION(33:92)CHAR
)

... and join them together using SQL:

insert into STG_AM02_BA_RAW
    (
    subscriber_no,
    account_no,
    subscriber_name,
    input_filename
    )
select
    d.subscriber_no,
    d.account_no,
    d.subscriber_name,
    f.inputfile
from
    stage_data d,
    inputfile d

This process falls over if you have concurrent loads.

You said in the comments that you can change the data file - could you get the file changed to that the filename is appended to each record? If so, makes the issue go away. You'd just have to include:

    SUBSCRIBER_NAME                 POSITION(92:*)CHAR