I have a folder on a Linux server, that has been mapped to an Oracle database directory. Database is version 12.1.0.2.0. I am attempting to use a modified version Tom Kyte's method of getting the directory via an external table with a pre-loader shell script to provide the data fro the external table..
The database folder that is mapped to the OS folder is called test_out_dir. The OS folder exists and contains a number of document files.
Shell script definition:
$ cat >/test/bin/readdir.sh<<eof
#/bin/bash
cd /test_case/documents/
/bin/ls -1
eof
Due to concerns of my DBA and network folks, this script needs to exist in a separate folder than the location of documents. The shell script is in the /test/bin folder.
I tried defining the external table like this:
create table upload_files (filename varchar2(255))
organization external (
type oracle_loader
default directory TEST_OUT_DIR
access parameters (
records delimited by newline
preprocessor 'readdir.sh'
fields terminated by "|" ldrtrim
)
location ('/test/bin/readdir.sh')
);
There are no errors when I create the table but when I try and query the table I get the following error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04087: no execute access to directory object TEST_OUT_DIR
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
Is it allowable to have the shell script used in this manner to be in a different folder than the folder holding the directory objects being listed by the shell script?!
Thanks for any help!
Tony Miller
Los Alamos, NM