Can you add filter for procedures owned by RXREPUSER? In the result set you show I can’t see which procedures should be displayed. Only the procedures owned by RXREPUSER should be displayed.
WHERE object_type = ‘PROCEDURE’
and OWNER = ‘RXREPUSER’
ORDER BY owner, object_name
Go to Options | Database | Cache and press the ClearCache button Then go back to the Object explorer and from the View menu request a Refresh ALL. Sft+F5
I want to make sure there isn't any cached data tripping us up. After that go to the Output window and confirm it is executing the correct query. You should still have on tracing.
Clearing cache didn’t make a difference.
I think the difference is between reading sys.all_objects & sys.dba_objects
sys.all_objects returns 1 row
sys.dba_objects returns 123 rows.
Object explorer is executing this code:
SELECT username, ’ ’ account_status FROM all_users a ORDER BY username Executed on SID 869:
SELECT o.object_name, o.object_type, DECODE (o.status, ‘VALID’, ‘true’, ‘false’) valid, o.last_ddl_time, o.object_id, o.created, NVL((select DECODE(NVL (c.debuginfo, ‘F’), ‘T’, ‘true’, ‘F’, ‘false’) from sys.all_probe_objects c where c.object_id = o.object_id), ‘false’) debuginfo, (SELECT NVL (p.AUTHID, ‘CURRENT_USER’) AUTHID FROM SYS.all_procedures p WHERE owner = :f1 and object_name(+) = o.object_name GROUP BY object_name, AUTHID) AUTHID , STATUS FROM SYS.all_objects o WHERE o.owner = :f1 AND object_type = ‘PROCEDURE’ ORDER BY 2, 1 Executed on SID 869:
f1:RXREPUSER
It reads from sys.all_objects
Application Data Dictionary - SqlDictionary.xml contains:
<?xml version="1.0"?>- - SELECT object_name, object_type, DECODE (status,‘VALID’, ‘true’, ‘false’) valid, last_ddl_time, object_id, created, NVL((SELECT DECODE(NVL(c.debuginfo, ‘F’),‘T’, ‘true’, ‘F’,‘false’)FROM sys.all_probe_objects c WHERE c.object_id = o.object_id), ‘false’), STATUS, owner FROM SYS.dba_objects o WHERE object_type = ‘PROCEDURE’ ORDER BY owner, object_name
It reads from sys.dba_objects.
Then the issue is that we still are not using the SQL Dictionary query. Let's have you edit the SQL Dictionary by hand.
Go to Tools | Options | Database | SQL Dictionary. On the right hand side look for DB.Common.SQL then expand to find List procedures.
From the Selection drop down choose the Oracle 9.0: NONDBA. Edit this SQL to use DBA_OBJECTS.
Great - That got the procedures to display.
Which selection controls functions, packages, triggers, & DB links? Those four object types have the same problem.
The other object types (mViews, procedures, tables, users, & views) work fine.
Our DBA has explained that we have SELECT_CATALOG_ROLE which allows us to see the view DBA_OBJECTS.
Since we don’t have execute privileges to the production procedures, those procedures are not in the view ALL_OBJECTS.
I’m learning more all the time.
Yey, on see the procedures. Got it on the Select_catalog_role. I have not run into this before. For the other object types look in the SQL Dictionary under the types that start with “Oracle.trl.%ObjectType%”.
Thanks for the help.
This will work in the short term, but we’ve got a number of users, and it’ll be more trouble to go thru everyone’s laptop to make the updates.
Will there be an updated version coming that’ll include this? A small number of us also have Toad for Oracle, and we don’t see the same issue on that product, so I’m assuming that it is querying DBA_OBJECTS.
Once you have successfully changed the queries it will write these into a SQL Dictionary.xml file. You can distribute this to your users. That is what I was trying to do for you in the beginning but I must not have gotten the SQL correctly.
I did enter an issue ticket, QAT-1880 to try an fix for this next release.
I’ve fixed several object types.
I’m still having problems with Packages, Triggers, & DB Links
Can you point me to the code for them?
Concerning DB_links:
I copied the code, and ran it manually, and it returned two DB_links – However the Navigation Manager only returned the public one.
I don’t understand the filter portion of the code. I commented it out because it would not run in the editor. Is it restricting the set of rows returned?
SELECT {{Count(Start)}} OWNER, DB_LINK {{Count(End)}}
from SYS.DBA_DB_LINKS
WHERE OWNER IN (:f1, ‘PUBLIC’) {{Filter}} {{Count(Start)}}
ORDER BY DB_LINK {{Count(End)}}
20140214_toad_data_point_screenshot_A.doc (104 KB)
The odd parenthesis pairs are our own internal SQL Functions and are needed in your query when placed in the SQL Dictionary.
I see that there is also a DB Link info query. Let's change that query to use the DBA views also.
If this doesn't work I have one other idea but let's really make sure we can't handle this through manipulating the SQL Dictionary.
I already changed that one too:
SELECT OWNER “Link Owner”, DB_LINK “Link Name”, HOST “Host”, USERNAME “User Name”, CREATED “Date Created” FROM SYS.DBA_DB_LINKS WHERE OWNER = :f1 AND DB_LINK = :f2
Okay, I have an idea that will take a code change. I entered this as QAT-1880 and scheduled for week of 2/24 - 2/28. I will try to get a one-fix fix for you to try.
This did make it into the TDP 3.5 release.
