Problem with dbms_metadata.get_ddl

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  April 12, 2013 at 10:16 am

  #272942

  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/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 12, 2013 at 11:12 am

  #1606052

  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

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  April 12, 2013 at 11:35 am

  #1606072

  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/