question

KillerDBA avatar image
KillerDBA asked

How to get information about objects?

How do you get information about objects in Oracle? The methods I've found are either primitive (query a system table) or don't work.

For example:

SELECT DBMS_METADATA.GET_DDL('VIEW','myview') FROM DUAL;

doesn't work, although I just now created myview.

helpmetadataobjectsdefinition
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Jonah H. Harris avatar image
Jonah H. Harris answered 
SELECT DBMS_METADATA.GET_DDL('VIEW','MYVIEW') FROM DUAL;
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA avatar image KillerDBA commented ·
Thanks to your hint and some testing, I see... Or I think I do... Whether or not I create the view with uppercase name, I must use uppercase in the select. I don't mind saying, this bites.
0 Likes 0 ·
Jonah H. Harris avatar image Jonah H. Harris commented ·
Yep, the metadata calls require the object name to be in uppercase.
0 Likes 0 ·
KillerDBA avatar image
KillerDBA answered

To answer my own question, SQLTools seems to be very helpful for retrieving objects definitions. The price is right ($free). Looks promising for development work, too.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Atkinson avatar image David Atkinson commented ·
Looks good. Does this do more than SQL Developer?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.