Hi,
The objective is to unload data using external table (oracle_datapump) from Oracle package.
But, when trying to execute from a package using execute immediate it fails with below error message.
It works fine when running as anonymous pl/sql block or from sqlplus using create table organization external as select...
Please help on why we get this error when executing from package.
create or replace package test_ext_pkg as
procedure create_tab;
lv_create_tab_sql varchar2(1000) :=
'create table test_ext
organization external
(
type oracle_datapump
default directory TESTLOG
access parameters (
nologfile
compression enabled medium
)
location (''test_ext.dmp'')
)
as
select * from dual
';
end test_ext_pkg;
/
create or replace package body test_ext_pkg as
procedure create_tab as
l_exec_sql varchar2(1000) := test_ext_pkg.lv_create_tab_sql;
begin
execute immediate l_exec_sql;
end create_tab;
end test_ext_pkg;
/
begin
test_ext_pkg.create_tab;
end;
/
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04064: fatal error during initialization
ORA-06512: at "TEST_EXT_PKG", line 5
ORA-06512: at line 2
Create table script works in SQLPLUS
create table test_ext
organization external
(
type oracle_datapump
default directory TESTLOG
access parameters (
nologfile
compression enabled medium
)
location ('test_ext.dmp')
)
as
select * from dual
/
Table created.
The Anonymous pl/sql block also works fine
declare
lv_create_tab_sql varchar2(1000) :=
'create table test_ext
organization external
(
type oracle_datapump
default directory TESTLOG
access parameters (
nologfile
compression enabled medium
)
location (''test_ext.dmp'')
)
as
select * from dual
';
begin
execute immediate lv_create_tab_sql;
end;
/
PL/SQL procedure successfully completed.
SQL> desc test_ext
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
Sorry, I'm not seeing this behaviour.
And the error message for KUP-04064: fatal error during initialization reads:
Cause: An unexpected error occurred while initializing access to an external table.
Action: Contact Oracle support.
Take this one up with support.