I am trying to view the script behind a View created in our Oracle database. I keep getting the following error:
“The requested database object information has not been retrieved. Object information is retrieved by SYS.DBMS_METADATA. Check to see that you have privileges for this package.”
I’ve been told that this has to be caused by some sort of setting in TOAD because if a different tool is used to access the database with the same credentials/authentication, there is no issue. Any ideas?
No, the issue is just what the message says. You do not have privileges to execute the DBMS_METADATA Oracle package. Toad Data Point uses that to get the view sql from Oracle. Other products, such as Toad for Oracle don’t use the package but execute queries to get the script info.
Can you ask your DBA to grant you privileges on this package?
Hi Debbie
Thank you for the reply. Unfortunately access to the METADATA package is something that no one in our company has access to, and it has caused quite a stir with me asking for it. That is locked down tight, and the user access that I have to our database is meant only to be a view only type access. However, if I use PL/SQL Developer for example with the exact same access user, I have no issue viewing the scripts for Views. So I don’t understand how it is an issue of not having access to the package. Because I don’t have access to that package in a different tool either. Or if you could please explain to me why I would need to have this access in order to utilize TOAD vs a different tool. Being able to view the scripts to Views is going to be a key learning tool for me. I apologize for coming back to this, however, it is extremely important that I figure out a solution to this issue.
Thank you!
Nicole
There is more than one way to get the content of a veiw. We use DBMS_META package because it does all of the work on the server. Other products, such as Toad for Oracle do not use this package and instead execute the query needed to get the content as well as other roles and privileges needed.
The purpose of the script tab normally is to regenerate the object. And regenerating the object is the task of a DBA. That is probably why you got the reaction you did.
If you execute this sql and enter the owner and view name, the text column will give you the view text. Note: you will need to have the Read Lobs option set on or press inside the text cell to retrieve the LOB.
select * from all_views
where owner = :a
and view_name = :b;