PL / SQL crashes when trying to expand a multiset collection returned in a cursor

[AGM]

I'm using PL/SQL Developer v10.0.5.1710 with Oracle 11g and win_32 client 11.2.0.
My PL/SQL code returns cursors with inner queries to retrieve collection fields using CAST MULTISET approach.
But when I inspect the cursor output and try to expand the collection field the PL/SQL Developer crashes.
The issue just happen with the IDE, because the expected return is right and our application consumes the cursor without any problem, but this issue makes hard to debug the output when we have this kind of queries.

This are the steps to reproduce using an example with similar output:


//Create the nested table used to return collection of values
CREATE OR REPLACE TYPE mytable_varchar AS TABLE OF VARCHAR2(32767);


Create our test procedure in a package, i.e pk_tests:

TYPE cursor_type IS REF CURSOR;

PROCEDURE test_cursor(o_cursor OUT cursor_type) IS

BEGIN
OPEN o_cursor FOR
SELECT ut.table_name,
CAST(MULTISET (SELECT ui.index_name
FROM user_indexes ui
WHERE ui.table_name = ut.table_name) AS mytable_varchar) index_names
FROM user_tables ut;

END test_cursor;

Now we create a Test window for our procedure test_cursor :


begin
pk_tests.test_cursor(o_cursor => _cursor);
end;

We expand the o_cursor variable and will see the two columns (TABLE_NAME and INDEX_NAMES).
If we click try to inspect the returned in a row of INDEX_NAMES column the PL/SQL Developer will close/crash without any other information.

If you need the file Debug.txt I also can provide it.

I hope this information help you to detect and fix it.
Thank you.

 

[Marco Kalter]

This works fine when I try it. Can you go to Help > Support Info, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?

 

[AGM]

Marco, I sent to you a PM with the support info.

The strange is that if the query runs in a SQL window I can inspect the content of collections, but the error happens when I try to inspect collections that are returned in an out cursor from a package procedure/function.

Running PL/SQLDeveloper with option DebugSQL the last entry before crash is always something like this:

8/12/2013 10:20:06 AM  NavigationBookmarks: Backward=4, Forward=0
8/12/2013 10:20:06 AM  TOracleQuery 071EC0 Start Query.Execute
                       SQL = select cursor(select * from table(cast(:obj as ALERT.MYTABLE_VARCHAR ))) cursor_field from dual
                       :OBJ = (Object)

 

[Roeland]

I have the exact same issue on an Oracle 12, but I can't reproduce it on Oracle 10 ?!?.

PL/SQL version 10.0.5.1710

 

[Mauro Sousa]

Hi,

Is there any solution for this problem to v.10.0.5.1710 already?

 

[AlexeyS]

Hi, i have same problem. PL/SQL Developer 12.0.7.1837. Oracle 11g with instantclient.
Any solution?

 

[Marco Kalter]

Can you go to Help > Support Info and send me the text from the "Info" tab page?

 

[AlexeyS]

Hm.. I install another version client: instantclient-basiclite-nt-12.2.0.1.0
And now all work fine in my case))