title: Oracle SQL save file name from LOCATION as a column in external table
body: <p>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 <strong>filter the data based on the input file it came from</strong>. 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. </p>
  
  <p>Here is an example:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Any suggestions?</p>
  
  <p>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.</p>