I am trying to get all XML file names present in a directory in order to feed them to a procedure which pulls data out of those files. Could anyone help with how I can get the file name using the EXTERNAL TABLE. I am having trouble with ACCESS PARAMETERS and LOCATION file. Don't know what exactly would go there.
CREATE TABLE S7303786.XML_FILES
( FILE_DATE VARCHAR2(50 CHAR),
FILE_TIME VARCHAR2(50 CHAR),
FILE_SIZE VARCHAR2(50 CHAR),
FILE_NAME VARCHAR2(255 CHAR) )
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY AUTOACCEPT_XMLDIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
LOAD WHEN file_size != '<DIR>'
PREPROCESSOR AUTOACCEPT_XMLDIR: 'list_files.sh'
FIELDS TERMINATED BY WHITESPACE
)
LOCATION ('sticky.txt')
)
REJECT LIMIT UNLIMITED;
list_files.sh just contains the directory where the files are present. sticky.txt has nothing in it
error I am getting are :
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04004: error while reading file /home/transfer/stu/nshstrans/sticky.txt