dbms_metadata.get_ddl not working

Question

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

I queried dba_objects it still got the 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 1

grant SELECT_CATALOG_ROLE to <user> with delegate option;

it work for me. Do this after modify procedure

grant SELECT_CATALOG_ROLE to procedure <procedure name>;

Answer 2

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.