Using DBMS_METADATA in Procedure to reference another schema's object

This is a question already asked by many and I thought I should give an example here. You want to call DBMS_METADATA in PL/SQL created on your schema, but referencing another users object and use the the error like:

*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "TEST.TEST_META", line 5
ORA-06512: at line 1


It is an expected behviour which is stated in the security Model for dbms_metadata in the Oracle® Database PL/SQL Packages and Types Reference
.

The SELECT_CATALOG_ROLE alone does not give you privilege to use it in a package/procedure for another schema. But it allows you to use it outside.

So you need to use the invokers-right by adding authid current_user

Example:

SQL> connect test/test
Connected.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),

SQL> create or replace procedure test_meta
2 as
3 v_str varchar2(32767);
4 begin
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') into v_str from dual;
6 dbms_output.put_line (v_str);
7 end;
8 /

Procedure created.

SQL> exec test_meta;
BEGIN test_meta; END;

*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "TEST.TEST_META", line 5
ORA-06512: at line 1

SQL> create or replace procedure test_meta authid current_user
2 as
3 v_str varchar2(32767);
4 begin
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMP', 'SCOTT') into v_str from dual;
6 dbms_output.put_line (v_str);
7 end;
8 /

Procedure created.

SQL> exec test_meta;

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL"
NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT
"BIN$Bj5vCCIBT6uv5ZCEA/Zm0A==$0" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10
INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE ROW MOVEMENT


PL/SQL procedure successfully completed.

SQL>