Accessing data filename from within SQL*Loader control file

Question

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 1

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

Solution 2

I don't think that there is a way to this in the circumstances you specified, AFAIK there is no way to properly reference the filename in the "data" part.

Couple of ideas for a workaround:

• Update the newly inserted records with a separate SQL statement. You might be able to build the statement from the batch file that invokes SQL*Loader.
• Modify the data file to include the filename (again, might be done from the batch file).

• Have the batch file build the control file to include the filename as a constant, so you could have something like

  INPUTFILE CONSTANT "my_data.dat"

Hope this helps.