How to generate an SQL file with DBMS_DATAPUMP

Question

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.

• user1822: Please show us the complete code that calls dbms_datapump
• Philᵀᴹ: Hi René, good to see you back. What Oracle version?
• René Nyffenegger: @Phil the version is 11.2.0.1
• Philᵀᴹ: As a workaround, replace KU$_FILE_TYPE_DUMP_FILE with 1 and KU$_FILE_TYPE_SQL_FILE with 4. Just while I think a bit more :)
• Philᵀᴹ: Oh, wait a second, do you need a separate handle for each file?

Answer 1

Ok, I didn't realise that creating such an export file consists of two steps. First, an ordinary dump file must be created, then this dump file is used to create an sql file.

Since I didn't have such a dump file, the script as posted would not work.

Here are the two steps, so that an sql file can be created

declare
 datapump_job number;
 job_state varchar2(20);
begin
 datapump_job := dbms_datapump.open(
 operation => 'EXPORT',
 job_mode => 'SCHEMA',
 remote_link => null,
 job_name => 'Export dump file',
 version => 'LATEST'
 );
 dbms_output.put_line('datapump_job: ' || datapump_job);
 dbms_datapump.add_file(
 handle => datapump_job,
 filename => 'export.dmp',
 directory => 'DATAPUMP_DIR',
 filetype => dbms_datapump.ku$_file_type_dump_file);
 dbms_datapump.start_job(
 handle => datapump_job,
 skip_current => 0,
 abort_step => 0);
 dbms_datapump.wait_for_job(datapump_job, job_state);
 dbms_output.put_line('Job state: ' || job_state);
 dbms_datapump.detach(datapump_job);
end;
/

and the script that creates the sql file:

declare
 datapump_job number;
 job_state varchar2(20);
begin
 datapump_job := dbms_datapump.open(
 operation => 'SQL_FILE',
 job_mode => 'SCHEMA',
 remote_link => null,
 job_name => 'Export SQL file',
 version => 'LATEST'
 );
 dbms_output.put_line('datapump_job: ' || datapump_job);
 dbms_datapump.add_file(
 handle => datapump_job,
 filename => 'export.dmp',
 directory => 'DATAPUMP_DIR',
 filetype => dbms_datapump.ku$_file_type_dump_file);
 dbms_datapump.add_file(
 handle => datapump_job,
 filename => 'schema.sql',
 directory => 'DATAPUMP_DIR',
 filetype => dbms_datapump.ku$_file_type_sql_file);
 dbms_datapump.start_job(
 handle => datapump_job,
 skip_current => 0,
 abort_step => 0);
 dbms_datapump.wait_for_job(datapump_job, job_state);
 dbms_output.put_line('Job state: ' || job_state);
 dbms_datapump.detach(datapump_job);
end;

• user1822: If you only do this to get the SQL for the schema, you might want to add dbms_datapump.data_filter(handle => datapump_job, name => 'INCLUDE_ROWS', value => 0); (before starting the export job). Using that, no data will be exported which should be faster. Depending on what kind of SQL script you are after, you should also look into dbms_metadata - it can generate the SQL for everything(?) without the need to export first.
• René Nyffenegger: @a_horse_with_no_name thanks for the tip, much appreciated!