Skip to main content
Fixed tags
Source Link
Kris Johnston
  • 1.2k
  • 1
  • 10
  • 26

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

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"

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
Source Link
Kris Johnston
  • 1.2k
  • 1
  • 10
  • 26

Permissions needed for dbms_metadata.get_ddl in a function

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"