I am attempting to create a external table that will return the names and creation dates of all the images in a directory.
System Info:
Oracle Unbreakable Linux 4.1.12-112.14.15.el7uek.x86_64
Oracle 12cR2 Standard One running on ASM Grid Infrastructure Standalone
OS Level Permissions the oracle and grid users share asmadmin
drwxrwxrwx 2 oracle asmadmin 84 May 10 13:59 stage
-rwxrwxrwx 1 oracle asmadmin 274 May 10 13:58 imagedir_ls.sh
-rwxrwxrwx 1 oracle asmadmin 3545358 May 10 10:24 IMG_0001.JPG
-rw-r--r-- 1 oracle asmadmin 762 May 10 13:59 script.log
-rwxrwxrwx 1 oracle asmadmin 0 May 10 11:19 sticky.txt
This is the imagedir_ls.sh script. For testing I reduced it down to just return test,test when ran from the OS level.
##arr=( $(/bin/ls -1 /stage/ |/bin/grep -i '.jpg$\|.png$\|.gif$') )
##for f in "${arr[@]}"; do
## x=(" $(/home/stage/load/bin/Image-ExifTool-10.75/exiftool /stage/$f -s -s -s -datetimeoriginal) ")
## r+=$f",\""$x"\""
## echo "$r"
## r=""
##done echo "test,test"
Created Directory as sys
create or replace directory TEST as '/stage';
grant read, write, execute on TEST to PHOTO;
As PHOTO
CREATE TABLE "IMAGEDIR_LS"
( "FILE_NAME" VARCHAR2(255),
"FILE_DATE" VARCHAR2(255) )
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR TEST:'imagedir_ls.sh'
logfile TEST:'script.log'
FIELDS terminated by ',' optionally enclosed by '"')
LOCATION (TEST : 'imagedir_ls.sh') )
REJECT LIMIT UNLIMITED ;
select * from imagedir_ls;
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04001: error opening file /stage/imagedir_ls.sh
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.
From the script.log file
LOG file opened at 05/10/18 14:34:28
KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.
Field Definitions for table IMAGEDIR_LS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
FILE_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
FILE_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace same as SQL Loader
KUP-04001: error opening file /stage/sticky.txt
KUP-04017: OS message: Resource temporarily unavailable
KUP-04118: operation "wait", location "skudmi:prp:6"
***I have dropped this table and created it without the preprocess using a standard text file and it works fine.
Thanks for any advice or solutions.