External table to list contents of a directory

SImple external table definition and shell script to list contents of a directory and display as a table.

create directory gold as 'your path here';

CREATE TABLE gold_files
   ( permissions VARCHAR2(50)
   , file_type VARCHAR2(50)
   , owner_name VARCHAR2(50)
   , group_name VARCHAR2(50)
   , filesize VARCHAR2(50)
   , date_created VARCHAR2(50)
   , time_created VARCHAR2(50)
   , file_name VARCHAR2(255)
   )
   ORGANIZATION EXTERNAL
  (
      TYPE ORACLE_LOADER
     DEFAULT DIRECTORY gold
     ACCESS PARAMETERS
     (
        RECORDS DELIMITED BY NEWLINE
        PREPROCESSOR gold: 'list_files_for_external_table.sh'
        FIELDS terminated by whitespace
     )
     LOCATION ('dummy.txt')
   )
   REJECT LIMIT UNLIMITED;
dummy.txt is just an empty file

list_files_for_external_table.sh contains the following (you have to use full paths for all commands):


/bin/ls -lrt /oracle/export/GOLD/*.dmp |/usr/bin/grep -v total

chmod 700 that file

You can then run:

SELECT * from GOLD_FILES;

Comments