How to generate an SQL file with DBMS_DATAPUMP

exportoracleoracle-11g-r2

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.

Best Answer

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;
Related Question