Creating an (import-) SQL file with DBMS_DATAPUMP

DBMS_DATAPUMP can create SQL files from a schema so that these files can later be run to re-create the schema.

This is described in Oracle Note 1519981.1: How to Generate A SQL File Using The DBMS_DATAPUMP_API?. Unfortunately, the note does not explicitely state that the creation of such an sql file consists of two steps, first the schema has to be dumped ordinarly, then, the dumped file has to be turned into the desired SQL file.

Here are the steps to create such an SQL file.

First step: creating the dump file

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; /

Second step: turning the dump file into an 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; /

Links

Source code on github

My question on dba.stackexchange.com

Cloning an Oracle schema with DBMS_DATAPUMP