4

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.

8
  • 1
    Please show us the complete code that calls dbms_datapump
    – user1822
    Feb 5, 2015 at 7:35
  • Hi René, good to see you back. What Oracle version?
    – Philᵀᴹ
    Feb 5, 2015 at 9:44
  • @Phil the version is 11.2.0.1 Feb 5, 2015 at 9:51
  • 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ᵀᴹ
    Feb 5, 2015 at 9:59
  • Oh, wait a second, do you need a separate handle for each file?
    – Philᵀᴹ
    Feb 5, 2015 at 9:59

1 Answer 1

2

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;
2
  • 3
    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.
    – user1822
    Feb 6, 2015 at 6:50
  • @a_horse_with_no_name thanks for the tip, much appreciated! Feb 6, 2015 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.