I have external table that cant run from procedure but can run from simple PL/SQL code.
DB is 12c, ran from common user with DBA privileges and of course read,write on this directory
Lets start from the end:
KUP-04087: no execute access to directory object SCRIPT_DIR
--pass
QL> declare
v_ind number;
begin
select count(*)
into v_ind
from PDBS_LIST
where PDB_NAME = 'CORE';
dbms_output.put_line(v_ind);
end; 2 3 4 5 6 7 8 9 10 11 12
13 /
0
PL/SQL procedure successfully completed.
-- did not pass
SQL> create procedure nir_test as
v_ind number;
begin select count(*)
into v_ind
from PDBS_LIST
where PDB_NAME = 'CORE';
end; 2 3 4 5 6 7 8 9 10
11 /
Procedure created.
SQL> exec nir_test;
BEGIN nir_test; END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04087: no execute access to directory object SCRIPT_DIR
ORA-06512: at "C##ADMIN.NIR_TEST", line 6
ORA-06512: at line 1
The table:
CREATE TABLE "C##ADMIN"."PDBS_LIST"
( "PDB_NAME" VARCHAR2(30 CHAR),
"CREATED_AT" CHAR(19 CHAR),
"SIZE_MB_ALLOC" NUMBER,
"SIZE_MB_USED" NUMBER
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "SCRIPT_DIR"
ACCESS PARAMETERS
( records delimited by newline
preprocessor SCRIPT_DIR:'get_pdbs_list.sh'
fields terminated by whitespace ldrtrim
)
LOCATION
( 'Do_NOT_Delete.txt'
)
);