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.
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;
Run as APP_OWNER:
This article is taken from my Oracle technical notes blog.