When clicking on the Procedure tab in Schema Browser, Toad hangs and never returns the list of procedures.
Firewall option that perform packet inspection on sqlnet traffic was enabled which prevents some traffic from database server to not transmit to the client application.
The Toad query that hangs:
SELECT * FROM (
SELECT u.name owner,
DECODE(o.status, 1, 'V', 'I') status,
,NVL(pi.AUTHID, 'DEFINER') AUTHID
,NVL(i.debuginfo, 'F') debuginfo
FROM sys.obj$ o, sys.USER$ u
,(SELECT distinct obj#, 'T' debuginfo
WHERE part = 1) i
'DEFINER') AUTHID FROM sys.procedureinfo$
WHERE PROCEDURE# = 1
AND overload# = 0) pi
WHERE o.TYPE# = 7
AND o.owner# = u.USER#
AND o.linkname IS NULL
AND o.obj# = pi.obj# (+)
AND i.obj# (+) = o.obj#
AND o.name = 'COURTLIST'
and u.name = ':own'') p
order by 2
Peform packat captures to see if the traffic from the database server is successful in transmitting to the client application Toad for Oracle.
Check with the network administrator to disable SQLNet inspection on the firewall.
Agency Firewall (ASA5200) has the option to perform packet inspection on sqlnet traffic. It is turned on by default. DMZ webservers may experience the same type of problem as Toad.
Note: This problem may also occur in other features or tabs in Toad.