I am attempting to write a procedure (within a package) that calls
DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a procedure I get the following error.
[Error] Execution (1: 1): ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137 ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285 ORA-06512: at "UTILITY.MANAGE_SCHEMA", line 71 ORA-06512: at line 1
This error is being generated from the
DBMS_DATAPUMP.OPEN call. Since it works in the anonymous block, I'm assuming this is actually a permissions issue (i.e. one or more permissions granted via a role are required). However, I can't find any documentation about what permissions are necessary to use datapump.
This problem boils down to: which permissions need to be explicitly granted to a user/schema in order for a package in that schema to be able to call
The owner of the package has both the
EXP_FULL_DATABASE roles, as well as the
The procedure code is below.
PROCEDURE copy_schema (p_source_schema VARCHAR2, p_target_schema VARCHAR2, p_asynchronous BOOLEAN := FALSE, p_link_name VARCHAR2 := 'prddb') IS dph NUMBER; v_source_schema VARCHAR2 (30) := UPPER (DBMS_ASSERT.simple_sql_name (p_source_schema)); v_target_schema VARCHAR2 (30) := UPPER (DBMS_ASSERT.simple_sql_name (p_target_schema)); v_link_name VARCHAR2 (30) := UPPER (DBMS_ASSERT.qualified_sql_name (p_link_name)); v_job_name VARCHAR2 (30) := UPPER ('IMPORT_' || p_target_schema); v_state VARCHAR2 (30); BEGIN DBMS_OUTPUT.put_line ( 'Starting copy: source_schema = ' || v_source_schema || '; target_schema = ' || v_target_schema || '; link_name = ' || v_link_name || '; job_name = ' || v_job_name); dph := DBMS_DATAPUMP.open ('IMPORT', 'SCHEMA', v_link_name, v_job_name); DBMS_OUTPUT.put_line ('dph = ' || dph); DBMS_DATAPUMP.metadata_filter (dph, 'SCHEMA_LIST', '''' || v_source_schema || ''''); DBMS_DATAPUMP.metadata_remap (dph, 'REMAP_SCHEMA', v_source_schema, v_target_schema); DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE'); DBMS_DATAPUMP.create_job_view (dph, UPPER (v_job_name || '_vw')); DBMS_DATAPUMP.start_job (dph); IF NOT p_asynchronous THEN DBMS_DATAPUMP.wait_for_job (dph, v_state); END IF; END copy_schema;
I have found one solution to creating a procedure that calls datapump, but it solves the problem by putting the procedure in the
SYSTEM schema, which I would prefer not to do.