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';
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;
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 filelist_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
Post a Comment