Oracle external tables - specifying a dynamic file name

Question

CREATE TABLE LOG_FILES ( LOG_DTM VARCHAR(18), LOG_TXT VARCHAR(300) ) ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER DEFAULT DIRECTORY LOG_DIR ACCESS PARAMETERS( RECORDS DELIMITED BY NEWLINE FIELDS( LOG_DTM position(1:18), LOG_TXT position(19:300) ) ) LOCATION('logadm')) ) REJECT LIMIT UNLIMITED / 

LOG_DIR is the oracle directory that points to /u/logs/

The problem is that the contents of /u/logs/ looks like this:

 logadm_12012012.log logadm_13012012.log logadm_14012012.log logadm_15012012.log 

Can I specify a dynamic file location? that is, every time I run Select * from LOG_FILES , it should use the day's log file. (e.g. log_adm_DDMMYYYYY).

I know that I can use alter table log_files location ('logadm_15012012.log') , but I will not need to issue the alter command.

Any other features?

thanks

Answer 1

I am ashamed that you are using 10g. At 11g, we can associate the script pre-processor — the script wrapper — with an external table. In your case, you can run a script that will determine the last file, and then issue a copy command. Something like:

  cp logadm_15012012.log logadm 

Adrian Billington wrote about this feature here . Honestly, his essay is more useful than official documents .

But since you're all at 10g, you can either run the ALTER TABLE statement or use a scheduled task ( cron or something else) to synchronize the new file with a common name.