2

Could anyone help with my datapump export. I've tried many combinations. But, I'm always getting:

ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4932
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5768
ORA-06512: at line 38

Any advice on this?

CREATE TABLE foo
(
  bar VARCHAR2(10)
);

INSERT INTO FOO VALUES('hello');
INSERT INTO FOO VALUES('world');
COMMIT;


create or replace package pkg
 is
  function tst(p_val varchar2) return varchar2;
end pkg;
/

create or replace package body pkg
 as
function tst(p_val varchar2) return varchar2 is
begin
 return p_val;
end;
end pkg;
/



DECLARE 
  h1 NUMBER;
  l_status varchar2(200);
BEGIN

    h1 := DBMS_DATAPUMP.OPEN(
      operation => 'EXPORT', 
      job_mode => 'TABLE', 
      remote_link => NULL,
      job_name => 'exp_1', 
      version => 'LATEST'
    );

    DBMS_DATAPUMP.ADD_FILE(
      handle => h1, 
      filename => 'export.dmp', 
      reusefile => 1,
      directory => 'DBOUT',
      filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
      
    DBMS_DATAPUMP.ADD_FILE(
      handle => h1, 
      filename => 'export.log', 
      reusefile => 1,
      directory => 'DBOUT',
      filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
    );

    DBMS_DATAPUMP.METADATA_FILTER(
      handle => h1,
      name => 'NAME_EXPR',
      value => 'IN (SELECT table_name FROM user_tables WHERE table_name LIKE ''FOO%'')',
      object_type => 'TABLE'
    );

    DBMS_DATAPUMP.DATA_REMAP(
      handle => h1, 
      name => 'COLUMN_FUNCTION', 
      table_name => 'FOO', 
      column => 'BAR', 
      function => 'pkg.tst'
    );

    
   DBMS_DATAPUMP.start_job(h1);
   DBMS_DATAPUMP.wait_for_job(h1, l_status);
   dbms_output.put_line( l_status );

END;
/

Regarding the documentation, it should work. No?

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_DATAPUMP.html#GUID-2C61513F-9040-4CE3-8C22-ED1FA5FF81DD

Any suggestion is more than welcome,

Regards,

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
amir
  • 331
  • 2
  • 9
  • 20

1 Answers1

1

The FUNCTION argument must be the correct case, and the SCHEMA argument must be included and must also be the correct case. This procedure call worked for me:

DBMS_DATAPUMP.DATA_REMAP(
  handle => h1, 
  name => 'COLUMN_FUNCTION', 
  table_name => 'FOO', 
  column => 'BAR', 
  function => 'PKG.TST',
  schema => user
);

None of those limitations are mentioned in the documentation so I would say that your original code is correct and that this behavior is a bug.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Hello Jon Heller, Surprisingly, it worked when I added the schema and put the package Name and function in UPPERCASE. Thank you very much ! – amir Jan 16 '21 at 17:51
  • I'm getting the same issue ORA-39001: invalid argument value ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 4929 ORA-06512: at "SYS.DBMS_DATAPUMP", line 6378 and I've tried every combination I can think of including making sure package name is upper case and trying with and without schema. this is Oracle 19. hence the different line numbers in the stack trace. – GHZ Mar 30 '21 at 12:47
  • 1
    @GHZ I just retested the code on 19c on Windows, and it works for me. You may have to create a support request through support.oracle.com - there may be some other bugs in this package that depend on your precise version or platform. – Jon Heller Apr 01 '21 at 03:53
  • @JonHeller Thanks. Yea it seems to be related to the number of data_remap calls I was making. I had a few hundred and that always seemed to end with missing package function error (for the remap function) in the log file even though the function was there. When I reduced it say to 10 or 20 then it went through. I went back to using expdp and parameter files for now which is a shame as I can't autogenerate the list of remaps. Will create a support request. – GHZ Apr 04 '21 at 22:33