Problem with dbms_metadata.get_ddl

  • I get Data from some Oracles Database. We own the Data but the system was designed by an outside firm.

    They took away out permissions to browse the tables in a tool such as SQLDeveloper.

    I can get a list of table by executing the following command:

    select dbms_metadata.get_ddl('TABLE','TRANSACTION_FACT','RDB_DWH') from dual;

    I can get a tables column definitions by executing the following:

    DESCRIBE RDB_DWH.TRANSACTION_FACT

    But I need the Primary Key, Indexes as well, Unique Constraints, Foreign Key Constraint, etc.

    I execute the following command in an attempt to Column information for a specific table.

    So I try the dbms_metadata.get_ddl Package and I get the following error. RDB_DWH is the owner name and TRANSACTION_FACT is the table name.

    select dbms_metadata.get_ddl('TABLE','TRANSACTION_FACT','RDB_DWH') from dual;

    ORA-31603: object "TRANSACTION_FACT" of type TABLE not found in schema "RDB_DWH"

    ORA-06512: at "SYS.DBMS_METADATA", line 4018

    ORA-06512: at "SYS.DBMS_METADATA", line 5843

    ORA-06512: at line 1

    31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""

    *Cause: The specified object was not found in the database.

    *Action: Correct the object specification and try the call again.

    But even if that worked I would not get the PK, Indexes as well as all the constraints.

    I'm trying to get permissions. Currently no one in the company has permissions to brows tables, etc. SUbmitted a request but in the even that that does not happen I need to do it in SQL.

    Any help would be greatly appreciated.:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • A quick BING search (search string: ORACLE and INFORMATION_SCHEMA views) led me here, does this help? http://www.alberton.info/oracle_meta_info.html

  • Yeah, that worked.

    I googled it a lot but unfortunately I did not find that article or anything similar to it.

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply