Automating and delegating datapump imports with PL/SQL

Shabbir Ahmad ====>

To automate datapump export file 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');

Shabbir Ahmad ===>This article is taken from my Oracle technical notes blog.