SELECT o.*,
NVL (d.debuginfo, 'F') DEBUGINFO,
NVL (p.AUTHID, 'DEFINER') authid
FROM (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')
AND status = 'INVALID'
UNION ALL
SELECT :own owner,
object_name,
object_type,
DECODE (status, 'VALID', 'V', 'I') status,
last_ddl_time,
object_id,
created
FROM sys.user_objects o1
WHERE 1 = 1 AND object_type IN ('PACKAGE BODY', 'PACKAGE')
AND ( (STATUS = 'INVALID')
OR EXISTS
(SELECT 'x'
FROM sys.user_objects o2
WHERE o2.object_name = o1.object_name
AND o2.status = 'INVALID'
AND o2.object_type =
DECODE (o1.object_type,
'PACKAGE',
'PACKAGE BODY',
'PACKAGE BODY',
'PACKAGE')))) o,
sys.all_probe_objects d,
( SELECT object_name, AUTHID
FROM sys.user_procedures
WHERE 1 = 1
GROUP BY object_name, AUTHID) p
WHERE p.object_name(+) = o.object_name
AND o.OBJECT_ID = d.object_id(+)
AND d.owner(+) = :own
AND O.Object_Name = D.Object_Name(+)
AND ( (d.object_type IS NULL)
OR (d.object_type IN ('PACKAGE', 'PACKAGE BODY')))
ORDER BY 3, 2