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