Toad World® Forums

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


#1

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?


#2

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


#3

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.


#4

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.


#5

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


#6

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


#7

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.


#8

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


#9

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


#10

In Beta#3 - the problem persists.


#11

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:


#12

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


#13

Latest BETA 7.2.0.3836 also has this problem :frowning: