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?
Any suggestion is more than welcome,
Regards,
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.