Oracle external tables - Specifying dynamic filename

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 an oracle directory that points to /u/logs/

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

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

Is there any way i can specify the location of the file dynamically? i.e. every time i run Select * from LOG_FILES it should use the log file of the day. (e.g. log_adm_DDMMYYYYY).

I know i can use alter table log_files location ('logadm_15012012.log') but i would like not to have to issue the alter command.

Any other possibilities?

Thanks

Answer 1

It's a shame you're running 10g. On 11g we can associate a pre-processor script - a shell script - with an external table. In your case you could run a script which would figure out the latest file and then issue a copy command. Something like:

 cp logadm_15012012.log logadm

Adrian Billington has blogged about this feature here. Frankly his write-up is more helpful than the official docs.

But as you're on 10g all you can do is run the ALTER TABLE statement, or use a scheduled job (cron or whatever) to sync a new file with the generic name.