Hi
Find below
My SESSION PRIV
Procedure to EXPORT. ie. "expimpdp02"
ERROR Message while executing "expimpdp02"
ANONYMOUS PL/SQL BLOCK -- NO ERROR
Encountered an error message, while executing procedure "expimpdp02" . However, the same code is working fine, if it is an anonymous PL/SQL block, Why is that ?
Appreciate your input. Thanks.
(1) SESSION PRIV:
===================================
Note: CONNECT is the only ROLE is assigned to my account in addition to the following PRIVS.
CREATE ANALYTIC VIEW
CREATE HIERARCHY
CREATE ATTRIBUTE DIMENSION
SET CONTAINER
CREATE SQL TRANSLATION PROFILE
CREATE CUBE BUILD PROCESS
CREATE MEASURE FOLDER
CREATE CUBE
CREATE CUBE DIMENSION
CREATE MINING MODEL
CREATE ASSEMBLY
CREATE JOB
CREATE RULE
CREATE RULE SET
CREATE EVALUATION CONTEXT
GLOBAL QUERY REWRITE
QUERY REWRITE
CREATE INDEXTYPE
CREATE OPERATOR
CREATE LIBRARY
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE VIEW
CREATE PUBLIC SYNONYM
CREATE SYNONYM
CREATE CLUSTER
CREATE TABLE
CREATE SESSION
(2) expimpdp02 Procedure to EXPORT table using DATAPUMP.
====================================================
--CREATE or REPLACE PROCEDURE expimpdp02
--AS
declare
h1 number;
dir_name varchar2(30);
l_table_name varchar2(20);
begin
dir_name := 'DP';
h1 := dbms\_datapump.open (operation => 'EXPORT', job\_mode => 'TABLE' );
dbms\_output.put\_line(' Test001');
dbms\_datapump.add\_file (
handle => h1,
filename => 'demo02.dmp',
reusefile => 1, -- REUSE\_DUMPFILES=Y
directory => dir\_name,
filetype => DBMS\_DATAPUMP.KU$\_FILE\_TYPE\_DUMP\_FILE);
dbms\_output.put\_line(' Test002');
dbms\_datapump.add\_file (
handle => h1,
filename => 'demo02.log',
directory => dir\_name,
filetype => DBMS\_DATAPUMP.KU$\_FILE\_TYPE\_LOG\_FILE);
dbms\_output.put\_line(' Test003');
-- CONTENT = DATA\_ONLY
dbms\_datapump.set\_parameter (
handle => h1,
name => 'INCLUDE\_METADATA',
value => 0);
dbms\_output.put\_line(' Test004');
dbms\_datapump.metadata\_filter (
handle => h1,
name => 'SCHEMA\_EXPR',
value => 'IN (''SCOTT'')' );
dbms\_output.put\_line(' Test005');
dbms\_datapump.metadata\_filter (
handle => h1,
name => 'NAME\_EXPR',
value => 'IN (''T1'')' );
-- dbms_datapump.data_filter (
-- handle => h1,
-- name => 'PARTITION_LIST',
-- value => x.partition_name,
-- table_name => x.table_name,
-- schema_name => x.table_owner);
dbms_output.put_line(' Test006');
dbms_datapump.start_job (handle => h1);
-- Pause for 1 second.
-- sys.dbms\_session.sleep(10);
dbms\_output.put\_line(' Test007');
dbms\_datapump.detach (handle => h1);
end;
/
(3) ERROR MESSAGE while executing .
======================================
SQL> exec EXPIMPDP02;
Error starting at line : 99 in command -
BEGIN EXPIMPDP02; END;
Error report -
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1849
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6832
ORA-06512: at "SCOTT.EXPIMPDP02", line 19
ORA-06512: at line 1
31626. 00000 - "job does not exist"
*Cause: An invalid reference to a job which is no longer executing,
is not 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.
(4) ANONYMOUS PL/SQL BLOCK -- NO ERROR
=================================
SQL> --CREATE or REPLACE PROCEDURE expimpdp02
SQL> --AS
SQL> declare
2 h1 number;
3 dir_name varchar2(30);
4 l_table_name varchar2(20);
5 begin
6
7 dir_name := 'DP';
16
17 h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'TABLE' );
18
19 dbms_output.put_line(' Test001');
20
22 dbms_datapump.add_file (
23 handle => h1,
24 filename => 'demo02.dmp',
25 reusefile => 1, -- REUSE_DUMPFILES=Y
26 directory => dir_name,
27 filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
28
29 dbms_output.put_line(' Test002');
30
31 dbms_datapump.add_file (
32 handle => h1,
33 filename => 'demo02.log',
34 directory => dir_name,
35 filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
36
37 dbms_output.put_line(' Test003');
38 -- CONTENT = DATA_ONLY
39 dbms_datapump.set_parameter (
40 handle => h1,
41 name => 'INCLUDE_METADATA',
42 value => 0);
43
44 dbms_output.put_line(' Test004');
45 dbms_datapump.metadata_filter (
46 handle => h1,
47 name => 'SCHEMA_EXPR',
48 value => 'IN (''CFM'')' );
49
50 dbms_output.put_line(' Test005');
51 dbms_datapump.metadata_filter (
52 handle => h1,
53 name => 'NAME_EXPR',
54 value => 'IN (''T1'')' );
55
56 -- dbms_datapump.data_filter (
57 -- handle => h1,
58 -- name => 'PARTITION_LIST',
59 -- value => x.partition_name,
60 -- table_name => x.table_name,
61 -- schema_name => x.table_owner);
62 dbms_output.put_line(' Test006');
63 dbms_datapump.start_job (handle => h1);
64
65 -- Pause for 1 second.
66 -- sys.dbms_session.sleep(10);
67
68 dbms_output.put_line(' Test007');
69 dbms_datapump.detach (handle => h1);
70
71
72 end;
73 /
PL/SQL procedure successfully completed.
SQL>