jump to navigation

DBMS_METADATA GET_DDL : Avoid ORA-31603 error by using SELECT_CATALOG_ROLE May 8, 2010

Posted by sendtoshailesh in Uncategorized.
trackback
While extracting DDLs from command-line, I always find the interesting thing to learn. Below post is my another learning with the same.
This is about how to avoid ORA-31603 error while extracting DDLs of other schema objects even one have all the privileges on the objects. This is somethings dealing with database security and about one role SELECT_CATALOG_ROLE.
Lets start with example with formulating the problem…………….
I’m testing this with two sample schema HR and SCOTT on 10g DB.
Lets connect to HR and test wether this user have access to SCOTT EMP table or not.
SQL> conn hr/hr
Connected.
SQL> desc scott.emp
ERROR:
ORA-04043: object scott.emp does not exist
Oh no. Lets give all the privilege on EMP table to HR.
SQL> conn scott/tiger
Connected.
SQL> grant all on emp to hr;
Grant succeeded.
Hmmm………. Now hr have all the privilge on SCOTT.EMP table. So why cant HR extract the DDLs for the same. Lets see….
SQL> conn hr/hr
Connected.
SQL> select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;
ERROR:
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
no rows selected
OOOOOps……….. So why HR cant do this. Even he has all the privilege on SCOTT.EMP table. This is becasue HR needs SELECT_CATALOG_ROLE to browse the dictionaries as HR is not privileged user like DBA. When DBMS_METADATA package is called, it attempts to browse the dictionaries. If Calling use does not have privilege to browse the dictionary the it fires error by saying invisibility to the target object that is ORA-31603 error.
Now lets complete the loop by granting this role to HR.
SQL> conn sys as sysdba
Connected.
SQL> grant select_catalog_role to hr;
Grant succeeded.
Ready to test it.
SQL> conn hr/hr
Connected.
SQL> set head off
SQL> set long 40000
SQL> select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;
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 “PK_EMP” 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,
CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
REFERENCES “SCOTT”.”DEPT” (“DEPTNO”) 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”
Hurray !!
So to use DBMS_METADATA, one should have at least SELECT_CATALOG_ROLE. If user is already a DBA then it already have this role.
Thanks & Enjoy !!
Advertisements

Comments»

1. allen - July 20, 2010

i find an interesting thing while i executing the following code:
select dbms_metadata.get_ddl(‘PROCEDURE’,’PROCEDURE_NAME’,’USER’) from dual
when i execute this code in a sql window or a text window on PL/SQL Developer,it’s no problem.
but when i write that code in a procedure and execute this procedure,the following errors occured:
ORA-31603: object “PROCEDURE_NAME” of type PROCEDURE not found in schema “USER”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_METADATA”, line 653
ORA-06512: at “SYS
note:the schema “USER” have DBA and SELECT_CATALOG_ROLE roles.
i’m confused now,help me please.

sendtoshailesh - July 20, 2010

It should work fine with DBA and SELECT_CATALOG_ROLE. but question here is – are you executing the code with username USER in you case?

Jacques - October 15, 2012

Hi Allen,
I have the exact same issue as you. Have you ever resolved this?

The user does have the SELECT_CATELOGUE_ROLE and I can SELECT FROM DUAL fine. I get the error when creating a PL/SQL procedure though.

Any thoughts?

Jacques - October 15, 2012
2. Robke - September 25, 2010

The get_ddl interface is ‘meant for casual browsing, e.g. from SQLPlus’. For programmatic approach see fetch_ddl and related functions.

3. Satish Viswanathan - October 27, 2011

Thanks for the tip. After applying the grant it worked well in Oracle SQL Developer.

4. garry - March 15, 2012

thnx u dear.. for useful information

5. transvalores - October 9, 2014

I’m impressed, I have to admit. Rarely do I encounter a blog that’s both
educative and amusing, and without a doubt, you’ve hit the nail on the head.

The problem is something that too few men and women are speaking
intelligently about. I’m very happy that I stumbled across
this in my search for something regarding this.

6. swapnil - July 12, 2015

Saved my day…Thank you!!!

7. Brian R - January 7, 2016

Just wanted to thank you for this article. It was just what I was looking for.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: