Toad Data Point 3.3.0.1526 Professional Edition Trial
I cannot see any Oracle Packages or Functions in the Object Explorer tabs. Am I missing something?
How are you connecting to Oracle? If you are connecting using ODBC you will not see packages or functions. You will get the most features if you install an Oracle client and connect with that. You can use Oracle Direct Connect without a client but this is using TCP/IP and is subject to network interruptions. Hopefully you can install a client and then define a connection type of Oracle. P.S. If you are using Freeware you will not have this feature as freeware only supports ODBC.
I believe I’m connecting directly to Oracle?..I have an Oracle Client installed locally and I can see the Packages/Functions in Toad for Oracle on the same machine. TDP Connections dialog points to EOraCliHome.
When you are connected and look at the object explorer, so you have the tabs for functions, procedures, and packages like shown below?
can you post a screen shot of what you see?
Interesting. I need you to turn on some logging so we can capture the SQL that is causing this issue. Go to Tools | Execution trace and choose “To Output”. Then go to the view menu and choose the output window. Then reproduce the error. You should see the SQL that caused the error in the output window. Try executing that SQL in an editor (to confirm we have the right query) and then post this query.
An Oracle account with more privileges may be needed. I would try using the same account you’re using with ToadOra if you’re not already doing so.
SELECT o.object_name, o.object_type, CASE WHEN (o.valid1 = ‘false’) THEN ‘false’ WHEN (o.valid2 = ‘false’) THEN ‘false’ ELSE ‘true’ END 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 distinct NVL (p.AUTHID, ‘CURRENT_USER’) AUTHID FROM SYS.all_procedures p WHERE owner = :f1 and p.object_name(+) = o.object_name) AUTHID , CASE WHEN (o.valid1 = ‘false’) THEN ‘INVALID’ WHEN (o.valid2 = ‘false’) THEN ‘INVALID’ ELSE ‘VALID’ END STATUS_1 FROM (SELECT a.object_name, a.object_type, DECODE (NVL (a.status, ‘VALID’), ‘VALID’, ‘true’, ‘false’) valid1, (SELECT DECODE (NVL (b.status, ‘VALID’), ‘VALID’, ‘true’, ‘false’) FROM SYS.all_objects b WHERE b.owner = :f1 and b.object_type(+) = ‘PACKAGE BODY’ AND a.owner = b.owner(+) AND a.object_name = b.object_name(+)) valid2, a.last_ddl_time, a.object_id, a.created, a.status FROM SYS.all_objects a WHERE a.owner = :f1 AND a.object_type = ‘PACKAGE’) o ORDER BY 2, 1
Query yields no results in TDP or ToadOra.
I can, however, see the packages in ToadOra with the same user account.
That query should return rows. Do you know if you DBA has put any extra restrictions on packages?
When you ran the query did you provide the schema that owns the packages?
Also can you run these two queries and see if you get rows on both?
select count (*) from DBA_objects where object_type in (‘PACKAGE’, ‘PACKAGE BODY’) ;
select count (*) from ALL_objects where object_type in (‘PACKAGE’, ‘PACKAGE BODY’) ;
I don’t know of any restrictions. I keep coming back to the issue has be to be with TDP, as I can see the Packages in Toad for Oracle with no trouble.
I get results for both queries.
I am also having the same problem (without the single row subquery error): I can see packages in Toad for Oracle which I can’t see in Toad DP with the same account. I also get results with both queries above. Was this ever resolved?
Try this. Go to Options and locate the SQL Dictionary. Replace the Package List SQL for both the Orcle 9i DBA and NonDBA with the SQL below. Restart the app and try again. This changes the SQL we use to list the packages.
select o.object_name,
o.OBJECT_TYPE,
o.STATUS VALID,
o.LAST_DDL_TIME,
o.OBJECT_ID,
o.CREATED,
DECODE(NVL (debuginfo, 'F'),'T', 'true','F', 'false') DEBUGINFO,
NVL(d.debuginfo, 'F') DEBUGINFO, nvl(p.AUTHID, 'DEFINER') authid,
o.status STATUS_1
from (
Select :f1 owner, object_name, object_type, decode(status, 'VALID', 'true', 'false') status, last_ddl_time, object_id, created
from sys.user_objects
where 1=1
and object_type in ('PACKAGE', 'PACKAGE BODY')
) o, sys.all_probe_objects d
,(SELECT object_id, AUTHID
FROM sys.USER_PROCEDURES
where 1=1
AND subprogram_id = 0
AND object_type = 'PACKAGE'
GROUP BY object_id, AUTHID) p
where p.object_id (+) = o.object_id
and o.OBJECT_ID = d.object_id (+)
and d.owner (+) = :f1
And O.Object_Name = D.Object_Name (+)
And ((d.object_type is null) or (d.object_type in ('PACKAGE')))
order by 3, 2
Doesn’t seem to yield any results. Furthermore, once I applied this change, the Oracle.Trl.Procedures.PackageLi… {what’s the full name?} disappeared from this config list. Please advise.
Execute the SQL in an editor and see if you get any results. Replace bind vars with the schema that owns some packages.