Identifying dataset belongs to which file in external table.

CREATE TABLE my_exttable
(
  col1                VARCHAR2(4000 BYTE),
  col2                VARCHAR2(400 BYTE),
  col3                VARCHAR2(400 BYTE)

)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY mydir
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY NEWLINE
        BADFILE  mydir:'myfile.out.bad'
        DISCARDFILE mydir:'myfile.out.dsc'
        LOGFILE mydir:'myfile.out.log'
        FIELDS TERMINATED BY '|'
        MISSING FIELD VALUES ARE NULL
        (
col1,
col2,
col3
        )
         )
     LOCATION (mydir:'file1.out', mydir:'file2.out')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

create or replace procedure myproc(v_filename varchar2)
begin
      delete from mytable
      where file_name = v_filename
      and mdate <= sysdate; <--before inserting data, i need to delete the data that was added --if there is a rerun.

      insert into mytable
            select col1, col2, col3, v_filename from my_exttable;

      commit;
end;


begin
      myproc('file1');
end;

you could put a view on top of the 2 external tables so you only need to read one object.

or, in your procedure, issue an ALTER TABLE on the external table to change the location so that it only points to a single file at any one time, read the first file, change the location to point to the second file, read that one.

my preference would be to go with the 2 table method and a view.

There is a way, but it is tedious. should i implement this in production?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3015912000346648463

if you have 11.2 and have access to execute a preprocess instruction,  then why not do it?

don't have 11.2!
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

A preprocessor clause was accepted in my env!
ACCESS PARAMETERS
 16    ( RECORDS DELIMITED BY NEWLINE
 17          preprocessor  exec_dir:'run_gunzip.sh'
 18      FIELDS TERMINATED BY "|" LDRTRIM


However, i dont think such a thing will be allowed in production.
Also, will i need to do this each time data is refreshed in the external table everyday?
!file emp?.dat.gz
!cat run_gunzip.sh


This does not seem to be a solution but a workaround the issue.

It's an undocumented feature for 11.1 so it might not be supported.


I'm not sure what you mean by doing it each time.

"you" don't have to do anything,  the directive will run the script for you as it reads the files.

also I'm not sure why you're doing the run_gunzip  script,  what does compression have to do with this process?

As external table is like a view that dosen't consume any space, keeping one ET per file is not a bad idea.
As discussed i can create a composite view for each ET that contains the filename.

This design looks ok.