Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_DATAPUMP Procedure to Function

673227Nov 20 2009 — edited Nov 20 2009
Hi,

I have created a procedure that works totally fine as follows:
CREATE OR REPLACE PROCEDURE proc_expdp(p_schema_name VARCHAR2, p_table_name VARCHAR2)
IS
    v_handle NUMBER;
    v_jobname VARCHAR2 (100);
    v_dirname VARCHAR2 (100);
    v_filename VARCHAR2 (100);
BEGIN
    v_filename := to_char(sysdate,'YYYYMMDDHH24MISS')||'.DMP';
    v_jobname := v_filename||'_EXPDP';
    v_dirname := 'DMPDIR';
    v_handle := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', job_name => v_jobname);
    dbms_datapump.add_file(handle => v_handle, filename => v_filename, directory => v_dirname, filetype => 1);
    dbms_datapump.add_file(handle => v_handle, filename => v_filename||'_EXPDP'||'.LOG', directory => v_dirname, filetype => 3);
    dbms_datapump.metadata_filter (handle => v_handle, name => 'SCHEMA_EXPR', value => 'IN ('||p_schema_name||')');
    dbms_datapump.metadata_filter (handle => v_handle, name => 'NAME_EXPR', value => 'IN ('||p_table_name||')');
--    dbms_datapump.data_filter (handle => v_handle, name => 'PARTITION_LIST', value => '''ODS_SLS_ITEM_DETAIL_20090101'', ''ODS_SLS_ITEM_DETAIL_20090102'', more here ''ODS_SLS_ITEM_DETAIL_20090227'', ''ODS_SLS_ITEM_DETAIL_20090228''', table_name => 'ODS_SLS_ITEM_DETAIL', schema_name => 'ODSPROD');
    dbms_datapump.start_job(v_handle);
    dbms_datapump.detach(v_handle);
END;
... and I use the following PL/SQL code to run it, which works fine too.
BEGIN
  proc_expdp('''HR''','''DEPARTMENTS'',''EMPLOYEES''');
END;
I needed this as a function, so I fixed the following:
CREATE OR REPLACE FUNCTION system.func_expdp(p_schema_name VARCHAR2, p_table_name VARCHAR2) RETURN VARCHAR2
IS
    v_handle NUMBER;
    v_jobname VARCHAR2 (100);
    v_dirname VARCHAR2 (100);
    v_filename VARCHAR2 (100);
BEGIN
    v_filename := to_char(sysdate,'YYYYMMDDHH24MISS')||'.DMP';
    v_jobname := v_filename||'_EXPDP';
    v_dirname := 'DMPDIR';
    v_handle := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', job_name => v_jobname);
    dbms_datapump.add_file(handle => v_handle, filename => v_filename, directory => v_dirname, filetype => 1);
    dbms_datapump.add_file(handle => v_handle, filename => v_filename||'_EXPDP'||'.LOG', directory => v_dirname, filetype => 3);
    dbms_datapump.metadata_filter (handle => v_handle, name => 'SCHEMA_EXPR', value => 'IN ('||p_schema_name||')');
    dbms_datapump.metadata_filter (handle => v_handle, name => 'NAME_EXPR', value => 'IN ('||p_table_name||')');
--    dbms_datapump.data_filter (handle => v_handle, name => 'PARTITION_LIST', value => '''ODS_SLS_ITEM_DETAIL_20090101'', ''ODS_SLS_ITEM_DETAIL_20090102'', more here ''ODS_SLS_ITEM_DETAIL_20090227'', ''ODS_SLS_ITEM_DETAIL_20090228''', table_name => 'ODS_SLS_ITEM_DETAIL', schema_name => 'ODSPROD');
    dbms_datapump.start_job(v_handle);
    dbms_datapump.detach(v_handle);
    return v_filename;
END func_expdp;
... and I should be able to run it with a select statement. Well, that part is the problem. When I try to run it as a select statement
select system.func_expdp('''HR''','''DEPARTMENTS'',''EMPLOYEES''') from dual;
I get the following error:
An error was encountered performing the requested operation:
ORA-31626:job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
ORA-06512: at "SYSTEM.FUNC_EXPDP", line 4356
ORA-06512: at "SYSTEM.FUNC_EXPDP", line 11
31626. 00000 - "job does not exist"
*Cause: An invalid reference to a job which is no longer executing, is no executing on the instance where the operation was attempted, or that does not have a valid Master Table. Refer to any following error messages for clarification.
*Action: Start a new job, or attach to an existing job that has a valid Master Table.
What should I do..?

Thanks for you time

PS: I'm using 10g Express Edition. Lately I have created and deleted several datapump jobs, but I think this is irrelevant since my procedure runs fine.
This post has been answered by Lakmal Rajapakse on Nov 20 2009
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 18 2009
Added on Nov 20 2009
1 comment
462 views