Oracle SQL save file name from LOCATION as a column in external table

Question

I have several input files being read into an external table in Oracle. I want to run some queries across the content from all the files, however, there are some queries where I would like to filter the data based on the input file it came from. Is there a way to access the name of the source file in a select statement against an external table or somehow create a column in the external table that includes the location source.

Here is an example:

    CREATE TABLE MY_TABLE (
            first_name CHAR(100 BYTES)
            last_name CHAR(100 BYTES)
    )
    ORGANIZATION EXTERNAL
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY TMP
    ACCESS PARAMETERS
    ( 
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        badfile       'my_table.bad'
        discardfile   'my_table.dsc'
        LOGFILE       'my_table.log'
        FIELDS terminated BY 0x'09' optionally enclosed BY '"' LRTRIM missing field   VALUES are NULL
        (
           first_name  char(100),
           last_name   
        )
    )
    LOCATION ( TMP:'file1.txt','file2.txt')
 )
 REJECT LIMIT 100;


 select distinct last_name 
 from MY_TABLE
 where location like 'file2.txt'  -- This is the part I don't know how to code

Any suggestions?

There is always the option to add the file name to the input file itself as an additional column. Ideally, I would like to avoid this work around.

Answer 1

The ALL_EXTERNAL_LOCATIONS data dictionary view contains information about external table locations. Also DBA_* and USER_* versions.

Edit: (It would help if I read the question thoroughly.)

You don't just want to read the location for the external table, you want to know which row came from which file. Basically, you need to:

1. Create a shell script that adds the file location to the file contents and sends them to stdin.
2. Add the PREPROCESSOR directive to your external table definition to execute the script.
3. Alter the external table definition to include a column to show the filename appended in the first step.

Here is an asktom article explaining it in detail.