Opening an object type that is a table of objects - hangs SQLnav

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:

ScreenShot061.jpeg

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?

Oh, yes and the OS on my PC is Win 8.1 (Maybe this has something to do with it).

We will look at this for sure Martin, thanks for your feedback.

It might be a problem with the OS, we didn’t do so much testing on win8 yet, but we will still need to check first before confirm it.

So you are running in win 8 with both 7.0.1 and 7.1 beta, connected to 11gR2 database? May I ask which beta version are you running, 32 bit or 64 bit please? Thanks.

Yes, Win 8.1.

My oracle client is: 11gR2 32 bit.

First I installed the 64bit Beta, but it didn’t work, so I run 32bit Beta. But the bug is also happening on the 7.0.1.

Reproduced the bug on the same database with my co-workers PC.

The only difference he has is that he uses SQLnav 7.0.0.3028.

Ok I am clear now. Currently we do have compatibility issue in win8 with 64 bit build, so I was wondering how can you get it work, now it make sense that you are running 32 bit on it. :slight_smile:

Anyway for this open type issue, I have raised SQLNAV-1569 and will trace it for you.

Thanks again Martin,

Shirly

Hi Martin,

Can you please confirm that if this issue still exists in latest beta build please? For me it just takes a long time to open it but not hang. Did it hang for you and never come back?

Thanks,

Shirly

Hi Shirly. Unfortunately the issue persists also in the new Beta.

It may have to do something with the number of objects in the database. As I said before - it works fine on much much smaller database of the same version.

The freeze happens on a database where Oracle eBusiness Suite is installed so it has thousands of tables an other objects in it.

Ok thanks for your details Martin, we will continue detecting this problem.

Just tested this on Windows XP (32 bit) + SQLnav 6.2.1 - on the same database. Works fine.

In Beta#3 - the problem persists.

Thanks for your following Martin, but sorry we haven’t fixed it in beta3 yet, will let you know once it’s done.

Thanks and Enjoy your day. :slight_smile:

I can confirm that the BETA5 (64-bit) version also has this problem.

Latest BETA 7.2.0.3836 also has this problem :frowning: