Team,
please see below, able to run the shell script from the current file location, but failed when execution from External table.
could you please help us to understand what i am missing here?
(Environment is Oracle Exadata X7, with DB as 12.2)
create directory DIR1 as '/home/oracle';
grant read on directory DIR1 to c##rajesh;
grant write on directory DIR1 to c##rajesh;
grant execute on directory DIR1 to c##rajesh;
create directory EXEC_DIR as '/home/oracle/TEMP';
grant execute on directory EXEC_DIR to c##rajesh;
drop table emp_ext purge;
CREATE TABLE emp_ext
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DIR1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR EXEC_DIR:'read_zip.sh'
NOBADFILE
NOLOGFILE
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
)
location
(
'emp.zip'
)
)REJECT LIMIT UNLIMITED;
c##rajesh@PDB1> select * from emp_ext ;
select * from emp_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file read_zip.sh in /home/oracle/TEMP not found
c##rajesh@PDB1> ! ls -ltr /home/oracle/TEMP
total 4
-rwxrwxrwx 1 oracle oinstall 109 Jul 11 00:19 read_zip.sh
c##rajesh@PDB1> ! cat /home/oracle/TEMP/read_zip.sh
#!/bin/bash
x="$(/usr/bin/unzip -Z1 $1 | /bin/egrep -i 'emp[0-9]+.csv')"
# echo $x
/usr/bin/unzip -p $1 $x
c##rajesh@PDB1> ! ./home/oracle/TEMP/read_zip.sh /home/oracle/emp.zip
/bin/bash: ./home/oracle/TEMP/read_zip.sh: No such file or directory
c##rajesh@PDB1> host
[oracle@en701c202vm02 ~]$ ./home/oracle/TEMP/read_zip.sh /home/oracle/emp.zip
bash: ./home/oracle/TEMP/read_zip.sh: No such file or directory
[oracle@en701c202vm02 ~]$ cd /home/oracle/TEMP
[oracle@en701c202vm02 TEMP]$ ls -ltr
total 4
-rwxrwxrwx 1 oracle oinstall 109 Jul 11 00:19 read_zip.sh
[oracle@en701c202vm02 TEMP]$ ./read_zip.sh /home/oracle/emp.zip
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-1980 12:00:00 AM,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-1981 12:00:00 AM,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-1981 12:00:00 AM,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-1981 12:00:00 AM,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-1981 12:00:00 AM,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-1981 12:00:00 AM,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-1981 12:00:00 AM,2450,,10
7788,"SCOTT","ANALYST",7566,09-DEC-1982 12:00:00 AM,3000,,20
7839,"KING","PRESIDENT",,17-NOV-1981 12:00:00 AM,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-1981 12:00:00 AM,1500,0,30
7876,"ADAMS","CLERK",7788,12-JAN-1983 12:00:00 AM,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-1981 12:00:00 AM,950,,30
7902,"FORD","ANALYST",7566,03-DEC-1981 12:00:00 AM,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-1982 12:00:00 AM,1300,,10
[oracle@en701c202vm02 TEMP]$