Skip to Main Content
  • Questions
  • Unloading data using external tables in packages - error

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: August 20, 2020 - 2:48 pm UTC

Last updated: September 22, 2020 - 7:48 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

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>


and Chris said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SR created

A reader, September 01, 2020 - 9:40 am UTC

Hi,

Thanks for the reply. I have submitted SR with Oracle support, SR 3-23911651641

Thanks
Arun
Connor McDonald
September 02, 2020 - 12:05 am UTC

Please keep us update - we'd like to see what the problem was

Issue resolved

Arun, September 21, 2020 - 11:24 am UTC

Hi,

The issue was resolved after modifying the package to be an invoker's rights by using the AUTHID CURRENT_USER.

Thanks
Arun
Connor McDonald
September 22, 2020 - 7:48 am UTC

Thanks for getting back to us!


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.