Automating and delegating datapump imports with PL/SQL

We recently bought another company and we are in the process of integrating our systems with theirs. One requirement was to import their daily business data into our Business Intelligence system for reporting. We agreed they would provide an Oracle datapump export file for us to import. We wanted to automate this and have a non-privileged application owner perform the import. I created a stored procedure that could run the import as the system user and granted execute on the procedure to the application owner. The dbms_datapump package has lots of options and it was fun exploring them to find the best way of doing this.

Setup –

Run as sys :

 grant execute on dbms_lock to system;

Run as system:

CREATE OR REPLACE DIRECTORY SOURCE AS '/nfsmount/exp/incoming';
CREATE OR REPLACE PROCEDURE SYSTEM.SCHEMA_IMPORT (
 dumpfilename IN VARCHAR2)
IS
 h1 NUMBER; -- data pump job handle
 job_state VARCHAR2 (30);
 status ku$_Status; -- data pump status
 job_not_exist EXCEPTION;
 PRAGMA EXCEPTION_INIT (job_not_exist, -31626);
BEGIN
 h1 :=
 DBMS_DATAPUMP.open (operation => 'IMPORT',
   job_mode => 'SCHEMA',
   job_name => NULL);
 DBMS_DATAPUMP.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'TRUNCATE');
 DBMS_DATAPUMP.add_file (h1, dumpfilename, 'SOURCE');
 DBMS_DATAPUMP.add_file (h1,
   dumpfilename || '.log',
   'SOURCE',
   NULL,
   DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 DBMS_DATAPUMP.metadata_remap (h1,
   'REMAP_SCHEMA',
   'FROMSCHEMA',
   'TOSCHEMA');
 DBMS_DATAPUMP.start_job (h1);
 job_state := 'UNDEFINED';
BEGIN
 WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED')
 LOOP
 status :=
   DBMS_DATAPUMP.get_status (
   handle => h1,
   mask => DBMS_DATAPUMP.ku$_status_job_error
   + DBMS_DATAPUMP.ku$_status_job_status
   + DBMS_DATAPUMP.ku$_status_wip,
   timeout => -1);
 job_state := status.job_status.state;
 DBMS_LOCK.sleep (10);
 END LOOP;
 EXCEPTION
 WHEN job_not_exist
 THEN
 DBMS_OUTPUT.put_line ('job finished');
 END;
COMMIT;
END;
/
grant execute on schema_import to APP_OWNER;

Execution –

Run as APP_OWNER:

EXECUTE SYSTEM.SCHEMA_IMPORT('FILENAME.DMP')

Advertisements