I am trying to use DBMS_METADATA in a function but it does not work, only in direct SQL.
I was unable to find anything in the docs, just that SELECT_CATALOG_ROLE privilege is needed ( but of course my user has it ).
I know that privileges inside Pl/Sql are not the same as Sql but I cannot understand why this package cannot be used inside a function, it returns ORA-31603.
I tried to give this user every possible privilege ( directly, not through a role), no luck.
I even gave every privilege of SELECT_CATALOG_ROLE directly, well aware that privileges from roles are not active in Pl/Sql.
This is my code:
create or replace function scott.Get_Source ( Package_Name varchar2 ) return clob is
begin
return DBMS_METADATA.GET_DDL ('PACKAGE', Package_Name, 'USER1' );
end;
/where USER1 is the owner of the package
( in the final version it was supposed to be another input variable )
The only user with this is working is sys, I tried just for sake of curiosity, in a test db of course, but I strongly dislike the idea of messing with sys, even for a an innocent and simple function.
What am I missing?
If really DBMS_METADATA is designed for being used only in sql it should at least be documented
Best regards
Mauro
From the docs:
"The DBMS_METADATA package considers a privileged user to be one who is connected as user SYS or who has the SELECT_CATALOG_ROLE role. In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights."
SQL> create or replace
2 procedure p11(p_user varchar2) is
3 x clob;
4 begin
5 x := dbms_metadata.get_ddl('TABLE','EMP',p_user);
6 end;
7 /
Procedure created.
SQL>
SQL> exec p11(user)
PL/SQL procedure successfully completed.
SQL> exec p11('SCOTT')
BEGIN p11('SCOTT'); END;
*
ERROR at line 1:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2588
ORA-06512: at "SYS.DBMS_METADATA", line 2827
ORA-06512: at "SYS.DBMS_METADATA", line 3608
ORA-06512: at "SYS.DBMS_METADATA", line 5008
ORA-06512: at "SYS.DBMS_METADATA", line 5336
ORA-06512: at "SYS.DBMS_METADATA", line 6702
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at "MCDONAC.P11", line 4
ORA-06512: at line 1
SQL> desc scott.emp
Name Null? Type
----------------------------------------------------------------------- -------- ----------------
-------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> create or replace
2 procedure p11(p_user varchar2) authid current_user is
3 x clob;
4 begin
5 x := dbms_metadata.get_ddl('TABLE','EMP',p_user);
6 end;
7 /
Procedure created.
SQL> exec p11(user)
PL/SQL procedure successfully completed.
SQL> exec p11('SCOTT')
PL/SQL procedure successfully completed.