1

Oracle Enterprise 11.2.0.3

I'm receiving:

ORA-31603: object "string of type "string" not found in schema "string"

when executing a function that calls dbms_metadata.get_ddl.

I know that procedures/functions need have to have the permissions granted explicitly instead of through a role: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

I also know that the permissions needed to execute dbms_metadata.get_ddl is in the SELECT_CATALOG_ROLE role: https://sshailesh.wordpress.com/2010/05/08/dbms_metadata-get_ddl-avoid-ora-31603-error-by-using-select_catalog_role/

However, the SELECT_CATALOG_ROLE role contains over 2400 permissions. So, what are the explicit permissions needed to execute dbms_metadata.get_ddl so that I can call it from a function?

Test Case:

create or replace function getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

create table utilities.mytable (mycol varchar2(1));

select dbms_metadata.get_ddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
ORA-31603: object "MYTABLE" of type TABLE not found in schema "UTILITIES"

EDIT

So far, I have explicitly granted the following to the user that is owns getddl():

select any table
select any dictionary
2
  • Please check docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66868, especially part about invokers rights part.
    – Raj
    Commented Nov 5, 2015 at 19:38
  • @Raj Appreciate it. I didn't consider that... and didn't find it in my research. Based on your comment, I added the answer, but if you want to add your own answer, please do so, so that I can give you the points for it (and i'll delete my answer if you do). Commented Nov 5, 2015 at 20:38

2 Answers 2

0

Based on Raj's comment, the following works:

create or replace function utilities.getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
authid current_user
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>
0

Please check docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66868, especially part about invokers rights (added as an answer).

1
  • Please extract the relevant details from the link, and retain the link for further reading/give weight to your extract.
    – Erik
    Commented Nov 6, 2015 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.