0

I have created a view in Oracle which has about 15,000 lines (Most of them are case statements) in two databases. I am able to view the DDL of the view in the SQL tab of the Sql developer in one database. However, I am unable to view it in the second database.

I get the below few lines only in the SQL tab instead of the full view definition:

-- Unable to render VIEW DDL for object MY_VIEW with DBMS_METADATA attempting internal generator.
CREATE VIEW MY_VIEW
AS

I have given all types of grants for the view to public with no success. I have read that we can use GET_DDL function but I'd prefer to view in the SQL tab. Can anyone please help to view the DDL or is it any difference in the two databases?

Arun
  • 65
  • 1
  • 9
  • 1
    "However, I am unable to view it in the second database." >> Second schema or second database ? Thanks! – VBoka Jan 08 '20 at 11:29
  • 1
    Also, have you tried to run dbms_metadata.get_ddl ? Thanks! – VBoka Jan 08 '20 at 11:36
  • if you use the view in the second database, it works? – gsalem Jan 08 '20 at 12:15
  • @VBoka: Second database; I am not able to get through get_ddl. It says "does not exist" – Arun Jan 08 '20 at 13:13
  • @gsalem: I can use select statement on it and view it through the Data tab. – Arun Jan 08 '20 at 13:14
  • @Arun please can you give me the entire error not only "does not exist" ? Thanks! – VBoka Jan 08 '20 at 13:15
  • 1
    this is usually a privilege issue, the SQL page also uses dbms_metadata.get_ddl, so if that's not working, neither will the SQL page – thatjeffsmith Jan 08 '20 at 13:24
  • 1
    I believe this will help you: https://stackoverflow.com/questions/26026068/dbms-metadata-get-ddl-not-working – VBoka Jan 08 '20 at 13:29
  • @VBoka You're right. This seems like a privilege issue since I could view the DDL in DEV environment. This is the entire error which worked on first database but not in second. `ORA-31603: object "MY_VIEW" of type VIEW not found in schema "MY_SCHEMA" ORA-06512: at "SYS.DBMS_METADATA", line 5805 ORA-06512: at "SYS.DBMS_METADATA", line 8344 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.` – Arun Jan 08 '20 at 14:01
  • 1
    Check the link, test it as it says's there and see... Best of luck! – VBoka Jan 08 '20 at 14:07

0 Answers0