user2711819 user2711819 -4 years ago 70
SQL Question

dbms_metadata.get_ddl not working

I want to get the ddl of Table CARD_TABLE in XT schema

SQL> select dbms_metadata.get_ddl('TABLE','CARD_TABLE','XT') from dual;
ERROR:
ORA-31603: object "CARD_TABLE" of type TABLE not found in
schema "XT"
ORA-06512: at "SYS.DBMS_METADATA", line 5746
ORA-06512: at "SYS.DBMS_METADATA", line 8333
ORA-06512: at line 1


But my select Query works

select count(*) from XT.CARD_TABLE;
count(*)
---------
0


as requested i queried dba_objects it still got TABLE

SQL> select owner,object_type from DBA_OBJECTS
where object_name='CARD_TABLE' 2
3 ;


PUBLIC SYNONYM
XT TABLE PARTITION
XT TABLE PARTITION
XT TABLE PARTITION
XT TABLE
XT TABLE PARTITION
VAT TABLE

7 rows selected.

Answer Source

From the dbms_metadata documentation:

If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

So unless you're connected as a privileged user, you can't see the DDL for another user's objects. You would need to connect as SYS, or have the SELECT_CATALOG_ROLE role granted to your user to be able to get XT's object definition.

Even with that 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.

If you're calling dbms_metadata from an anonymous PL/SQL block that doesn't matter, but if you're calling it from a procedure you will have to include an AUTHID clause in the procedure declaration, adding AUTHID CURRENT_USER.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download