{"id":73267,"date":"2012-12-20T14:55:34","date_gmt":"2012-12-20T14:55:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/automating-and-delegating-datapump-imports-with-plsql\/"},"modified":"2021-07-14T13:07:47","modified_gmt":"2021-07-14T13:07:47","slug":"automating-and-delegating-datapump-imports-with-plsql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/automating-and-delegating-datapump-imports-with-plsql\/","title":{"rendered":"Automating and delegating datapump imports with PL\/SQL"},"content":{"rendered":"<p>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\u00a0exploring\u00a0them to find the best way of doing this.<\/p>\n<p>Setup &#8211;<\/p>\n<p>Run as sys :<\/p>\n<pre>\u00a0grant execute on dbms_lock to system;<\/pre>\n<p>Run as system:<\/p>\n<pre>CREATE OR REPLACE DIRECTORY SOURCE AS '\/nfsmount\/exp\/incoming';\r\nCREATE OR REPLACE PROCEDURE SYSTEM.SCHEMA_IMPORT (\r\n dumpfilename IN VARCHAR2)\r\nIS\r\n h1 NUMBER; -- data pump job handle\r\n job_state VARCHAR2 (30);\r\n  status ku$_Status; -- data pump status\r\n job_not_exist EXCEPTION;\r\n PRAGMA EXCEPTION_INIT (job_not_exist, -31626);\r\nBEGIN\r\n h1 :=\r\n DBMS_DATAPUMP.open (operation =&gt; 'IMPORT',\r\n   job_mode =&gt; 'SCHEMA',\r\n   job_name =&gt; NULL);\r\n DBMS_DATAPUMP.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'TRUNCATE');\r\n DBMS_DATAPUMP.add_file (h1, dumpfilename, 'SOURCE');\r\n DBMS_DATAPUMP.add_file (h1,\r\n   dumpfilename || '.log',\r\n   'SOURCE',\r\n   NULL,\r\n   DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);\r\n DBMS_DATAPUMP.metadata_remap (h1,\r\n   'REMAP_SCHEMA',\r\n   'FROMSCHEMA',\r\n   'TOSCHEMA');\r\n DBMS_DATAPUMP.start_job (h1);\r\n job_state := 'UNDEFINED';\r\nBEGIN\r\n WHILE (job_state != 'COMPLETED') AND (job_state != 'STOPPED')\r\n LOOP\r\n status :=\r\n   DBMS_DATAPUMP.get_status (\r\n   handle =&gt; h1,\r\n   mask =&gt; DBMS_DATAPUMP.ku$_status_job_error\r\n   + DBMS_DATAPUMP.ku$_status_job_status\r\n   + DBMS_DATAPUMP.ku$_status_wip,\r\n   timeout =&gt; -1);\r\n job_state := status.job_status.state;\r\n DBMS_LOCK.sleep (10);\r\n END LOOP;\r\n EXCEPTION\r\n WHEN job_not_exist\r\n THEN\r\n DBMS_OUTPUT.put_line ('job finished');\r\n END;\r\nCOMMIT;\r\nEND;\r\n\/\r\ngrant execute on schema_import to APP_OWNER;<\/pre>\n<p>Execution &#8211;<\/p>\n<p>Run as APP_OWNER:<\/p>\n<pre>EXECUTE SYSTEM.SCHEMA_IMPORT('FILENAME.DMP')<\/pre>\n<p>This article is taken from my <a href=\"http:\/\/steveharville.wordpress.com\/2012\/12\/18\/automating-and-delegating-datapump-imports-with-plsql\/\" title=\"Oracle technical notes\" target=\"_blank\">Oracle technical notes<\/a> blog.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 t&hellip;<\/p>\n","protected":false},"author":316185,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48393],"coauthors":[],"class_list":["post-73267","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-dbms_datapump"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73267","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316185"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73267"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73267\/revisions"}],"predecessor-version":[{"id":91737,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73267\/revisions\/91737"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73267"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73267"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73267"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73267"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}