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.