DBMS_DATAPUMP Procedure to Function

673227

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.

Lakmal Rajapakse

You can't use the function inside a SELECT statement because dbms_datapump.open performs an implicit commit. You could however use the function inside a pl/sql block like:

declare 
   l_filename varchar2(100); 
begin
   l_filename := system.func_expdp('''HR''','''DEPARTMENTS'',''EMPLOYEES''')
end;
/

Also please avoid creating any objects in the SYS or SYSTEM schemas.

Lakmal Rajapakse

You can't use the function inside a SELECT statement because dbms_datapump.open performs an implicit commit. You could however use the function inside a pl/sql block like:

declare 
   l_filename varchar2(100); 
begin
   l_filename := system.func_expdp('''HR''','''DEPARTMENTS'',''EMPLOYEES''')
end;
/

Also please avoid creating any objects in the SYS or SYSTEM schemas.