Example:
– Create Type:
CREATE OR REPLACE TYPE mpa_object_type
AS OBJECT
(id NUMBER
,description VARCHAR2(500)
);
– Create Type as table of Type:
CREATE OR REPLACE TYPE mpa_object_type_tbl
AS TABLE OF mpa_object_type;
– Ctrl+Click on the table type (Hangs SQLnav):
mpa_object_type_tbl;
– And neverending stoooooooory:
SQLnav is not responding.
Applies to Nav 7.0.1 and Beta 7.1.0.3435.
My DB version: 11.2.0.3.0.
Checked out the session - what is it doing.
Wait Event: latch: row cache objects
The running SQL was:
SELECT USER owner,
a.object_name,
a.object_id,
a.created,
a.last_ddl_time,
DECODE (a.status, ‘VALID’, 0, ‘INVALID’, 1, 2) status,
DECODE (b.typecode, ‘COLLECTION’, ‘Y’, ‘N’) collection,
DECODE (b.incomplete, ‘YES’, ‘Y’, ‘N’) incomplete,
b.attributes,
b.methods,
DECODE (c.coll_type, ‘TABLE’, 0, ‘VARYING ARRAY’, 1, 2) coll_type,
c.upper_bound,
c.elem_type_owner,
c.elem_type_name,
DECODE (c.elem_type_mod, ‘REF’, 1, ‘POINTER’, 2, 0) elem_type_mod,
c.LENGTH,
c.precision,
c.scale,
c.elem_storage,
DECODE (c.nulls_stored, ‘YES’, ‘Y’, ‘N’) nulls_stored
FROM sys.user_coll_types c, sys.user_types b, sys.user_objects a
WHERE b.type_name LIKE :object_name
AND a.object_name = b.type_name
AND a.object_type = ‘TYPE’
AND c.type_name(+) = a.object_name
AND c.elem_type_owner IS NOT NULL
UNION ALL
SELECT USER owner,
a.object_name,
a.object_id,
a.created,
a.last_ddl_time,
DECODE (a.status, ‘VALID’, 0, ‘INVALID’, 1, 2) status,
DECODE (b.typecode, ‘COLLECTION’, ‘Y’, ‘N’) collection,
DECODE (b.incomplete, ‘YES’, ‘Y’, ‘N’) incomplete,
b.attributes,
b.methods,
DECODE (c.coll_type, ‘TABLE’, 0, ‘VARYING ARRAY’, 1, 2) coll_type,
c.upper_bound,
c.elem_type_owner,
DECODE (c.elem_type_name,
‘TIMESTAMP WITH TZ’, ‘TIMESTAMP WITH TIME ZONE’,
‘TIMESTAMP WITH LOCAL TZ’, ‘TIMESTAMP WITH LOCAL TIME ZONE’,
c.elem_type_name)
elem_type_name,
DECODE (c.elem_type_mod, ‘REF’, 1, ‘POINTER’, 2, 0) elem_type_mod,
c.LENGTH,
c.precision,
c.scale,
c.elem_storage,
DECODE (c.nulls_stored, ‘YES’, ‘Y’, ‘N’) nulls_stored
FROM sys.user_coll_types c, sys.user_types b, sys.user_objects a
WHERE b.type_name LIKE :object_name
AND a.object_name = b.type_name
AND a.object_type = ‘TYPE’
AND c.type_name(+) = a.object_name
AND c.elem_type_owner IS NULL
UNION ALL
SELECT USER owner,
d.object_name,
d.object_id,
d.created,
d.last_ddl_time,
DECODE (d.status, ‘VALID’, 0, ‘INVALID’, 1, 2) status,
‘N’ collection,
‘N’ incomplete,
TO_NUMBER (NULL) attributes,
TO_NUMBER (NULL) methods,
2 coll_type,
TO_NUMBER (NULL) upper_bound,
TO_CHAR (NULL) elem_type_owner,
TO_CHAR (NULL) elem_type_name,
TO_NUMBER (NULL) elem_type_mod,
TO_NUMBER (NULL) LENGTH,
TO_NUMBER (NULL) precision,
TO_NUMBER (NULL) scale,
TO_CHAR (NULL) elem_storage,
‘N’ nulls_stored
FROM sys.user_objects d
WHERE d.object_type = ‘TYPE’
AND d.object_name LIKE :object_name
AND d.object_name NOT IN (SELECT type_name
FROM sys.user_types
GROUP BY type_name)
ORDER BY 2
When I bind the :object_name with ‘MPA_OBJECT_TYPE_TBL’ it quickly got me the results - 1 row.
Though It works fine on much much smaller database of the same version.
The only workaround is to find the object with the Search > Find Objects and Extract it’s DDL.
Can you look into this?