SQL & PL/SQL

Using Oracle 10.2.0.5

An external table is a construct that gives me SQL access to a file.

Is it possible to know the name of the file somehow inside the select? Like Add a column with the file name?

pseudo example

CREATE TABLE EXT_DUMMY
(
    "RECORDTYPE" VARCHAR2(100 BYTE), 
    "COL1" VARCHAR2(100 BYTE), 
    "COL2" VARCHAR2(100 BYTE), 
    "FILE" VARCHAR2(100 BYTE) 
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "IMPORT_BAD_FILE" 
    ACCESS PARAMETERS ( 
             records delimited BY newline 
             FIELDS TERMINATED BY ';' 
             MISSING FIELD VALUES ARE NULL 
               ( RECORDTYPE CHAR
               , COL1 CHAR 
               , COL2 CHAR 
               , FILE CHAR FILLER
               ) 
               ) 
    LOCATION ( 'Testfile1.txt, Testfile2.txt' )
)
    reject limit 10
;

The result could look like this:

RECORDTYPE   COL1       COL2      FILE
SAMPLE           DUMMY    DUMMY Testfile1.txt
SAMPLE           DUMMY1   DUMMY Testfile1.txt
SAMPLE           DUMMY2   DUMMY Testfile1.txt
SAMPLE           DUMMY3   DUMMY Testfile1.txt
SAMPLE           DUMMY1   DUMMY1 Testfile2.txt
SAMPLE           DUMMY1   DUMMY2 Testfile2.txt
SAMPLE           DUMMY2   DUMMY1 Testfile2.txt

I would like to know from which file a certain row is read. Maybe I missed an option in the documentation. In this example I have two different files as the source for the external table.

Another use case could be this:
If I enable a user to switch the external table to a different file

alter table EXT_DUMMY location ('Testfile3.txt' )

. How can we know which file is read during the select on the table? When userA does the select, maybe userB just altered the location before the select was started. Therefore userA would read in a different file then expected.

Edited by: Sven W. on May 26, 2011 4:48 PM

Edited by: Sven W. on May 26, 2011 4:51 PM

Edited by: Sven W. on May 26, 2011 5:11 PM