The Data Pump API allows the PL/SQL programmer to gain access to the data pump facility from inside PL/SQL code. All the features are available, so an export/import operation can be coded inside a stored procedure, thus allowing applications to perform their own programmed logical exports.
The stored program unit that leverages the data pump power is DBMS_DATAPUMP.
This code shows a simple export data pump job programmed with the DBMS_DATAPUMP API.
This sample code required the DBMS_DATAPUMP program units to perform the following tasks:
FUNCTION OPEN
PROCEDURE ADD_FILE
PROCEDURE METADATA_FILTER
PROCEDURE START_JOB
PROCEDURE DETACH
PROCEDURE STOP_JOB
The account used in the next example is used merely for demonstration purposes. In a practical scenario you can use any user that has the execute privilege granted on the DBMS_DATAPUMP package and the appropriate privileges on the working directories and target objects.
conn / as sysdba
set serveroutput on
DECLARE
dp_id NUMBER; -- job id
BEGIN
-- Defining an export DP job name and scope
dp_id := dbms_datapump.open('EXPORT','SCHEMA',NULL,'DP_API_EXP_DEMO','COMPATIBLE');
-- Adding the dump file
dbms_datapump.add_file(dp_id, 'shSchemaAPIDemo.dmp', 'DEFAULT_DP_DEST',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
-- Adding the log file
dbms_datapump.add_file(dp_id, 'shSchemaAPIDemo.log', 'DEFAULT_LOG_DEST',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
-- Specifying schema to export
dbms_datapump.metadata_filter(dp_id, 'SCHEMA_EXPR', 'IN (''SH'')');
-- Once defined, the job starts
dbms_datapump.start_job(dp_id);
-- Once the jobs has been started, the session is dettached. Progress can be monitored from dbms_datapump.get_status.
-- in case it is required, the job can be attached by means of the dbms_datapump.attach() function.
-- Detaching the Job, it will continue to work in background.
dbms_output.put_line('Detaching Job, it will run in background');
dbms_datapump.detach(dp_id);
-- In case an error is raise, the exception
-- is captured and processed.
EXCEPTION
WHEN OTHERS THEN
dbms_datapump.stop_job(dp_id);
END;
/