Toad World® Forums

Retrieve packages in grid error

HI,

Do not know since when is this code (saw in 12.9.0.9 and 12.9.0.12 x64), but just found out that it is not working on database version:

BANNER

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for HPUX: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Problem is in generated code that display packages:

with PACKAGES as
(Select :own owner, object_name, object_type, decode(status, ‘VALID’, ‘V’, ‘I’) status, last_ddl_time, object_id, created
from sys.user_objects
where 1=1
and object_type in (‘PACKAGE’, ‘PACKAGE BODY’))
SELECT PACKAGES.owner, PACKAGES.object_name, PACKAGES.object_type, PACKAGES.status,
PACKAGES.last_ddl_time, PACKAGES.object_id, PACKAGES.created
,NVL(pi.AUTHID, ‘DEFINER’) AUTHID
,NVL(d.debuginfo, ‘F’) DEBUGINFO
FROM PACKAGES
,(SELECT object_id, authid
FROM sys.user_procedures
WHERE subprogram_id = 0
AND object_type = ‘PACKAGE’
GROUP BY object_id, authid) pi
, sys.all_probe_objects d
WHERE PACKAGES.object_id = pi.object_id (+)
AND d.object_id (+) = PACKAGES.object_id
AND d.owner (+) = PACKAGES.object_name
AND d.object_name (+) = PACKAGES.object_name
AND d.object_type (+) in (‘PACKAGE’, ‘PACKAGE BODY’)
order by 3, 2

which is causing error:

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Same code is working on 11.2.0.3 database.
Brg
Damir

It’s been fixed for the next beta.