I am trying to generate an SQL file with the DBMS_DATAPUMP
api. This tasks is specifically adressed in Oracle support Note 1519981.1 (How To Generate A SQL File Using The DBMS_DATAPUMP API).
When I copy/paste the code found in the mentioned document, it fails on line 5 with a rather obscure
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3693
ORA-06512: at line 5
The line in question, that is line 5, is DBMS_DATAPUMP.add_file(...)
with the parameter filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE
.
I have run the script with a user that has DBA privileges.
I have also created an Oracle directory named MYDIR
. The OS directory it points to exists and is writeable.
The complete code that fails is (copied from the note):
DECLARE
h1 NUMBER;
BEGIN
h1 := DBMS_DATAPUMP.open(operation => 'SQL_FILE', job_mode => 'SCHEMA', job_name => 'j2');
DBMS_DATAPUMP.add_file(handle => h1,
filename => 'scott.dmp',
directory => 'MYDIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.add_file(handle => h1,
filename => 'scott.sql',
directory => 'MYDIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE );
DBMS_DATAPUMP.start_job(handle => h1, skip_current => 0, abort_step => 0);
DBMS_DATAPUMP.detach(handle => h1);
END;
/
The version is 11.2.0.1.
dbms_datapump
KU$_FILE_TYPE_DUMP_FILE
with1
andKU$_FILE_TYPE_SQL_FILE
with 4. Just while I think a bit more :)