In the debugger, when I rightclick on an array and then chose "view collection variable" pl/sql developer freezes. The array is of a schema level defined type.

Can you let me know your PL/SQL Developer version? Can you also let me know the definition of the array?

Oops, I slept on that one. But the Problem ist still there. I have Pl/SQL Developer 5.1.6.747 and it happens with all varrays (I Think), like a schema level defined "VARRAY(2147483647) OF NUMBER";

I the array defined within a package or through a "create or replace object" statement?

The Array is defined with:
CREATE OR REPLACE TYPE AR_NUM AS VARRAY(2147483647) OF NUMBER;
It shows up in pl/sql Developer in the "Types" Folder

Works fine for me. What is your server version?

This happens on Oracle 9.2.0.4
I am quite positive, that it happened on older versions as well. On Windows Nt Server and on Linux Machines. In Fact i have never seen it Working, except for PL/SQL Tables.

Where you able to reproduce the bug yet?

Not yet. It's quite odd that you have had this problem on various platforms / versions, and that we cannot reproduce it.

Seems odd, yes. I just tried a simple test case and the Problem is non existant then. I will try to find what breaks it.

OK, one case it happens is, when the array is a parameter.

I am also facing this problem which is seriously affecting my developement since the entire code is based on collections.
The "View Collection Variable" option used to work just fine till we upgraded our server from 8.1.7 to 9.2.0.4. Simultaneously I moved from Windows/2000 to XP.

Since then whenever viewing a collection variable, the entire pl/sql developer hangs.

Just today, the company upgraded to PL/SQL developer 5.1.6 version thinking the bug would be fixed in this version. But the thing still hangs.

I have bunch of collections of strictly scalar data type. Most of them are defined along following lines:

create or replace type MediumCharList as varray(4000) of varchar2(4000);

Variables used inside PL/SQL code are mostly of type MediumCharList.

The problem can be narrowed down to Oracle 9.2.0.1 specifically since none of the older and newer versions of PL/SQL developer now work for "View Collection" option. And before we migrated from Oracle 8.xx to 9.2, atleast the older versions of PL/SQL developer *were* working correctly. Any clues what it might be in Oracle 9.2.0.1 that is not allowing a successful "View Collection"?

No clue yet. What is your server operating system?

In the debugger, when I rightclick on an array and then chose "view collection variable"
pl/sql developer freezes.

- My PL/SQL Developer version is 6.0.0.810
- My OS is Windows XP Professional version 2002 (Service Pack 1)
- Database version is Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
- Clients versions: SQL*Plus: Release 9.2.0.1.0 or
SQL*Plus: Release 8.1.7.0.0 (two oracle homes)

** In debbuger example:

1. Type gt_Vetor Is Table Of VarChar2(50) Index By Binary_Integer;
2. Type gt_Matriz Is Table Of gt_Vetor Index By Binary_Integer;

3.
CREATE OR REPLACE Procedure PRC_TEST Is
vt_Test gt_Matriz;
begin
-- Load vt_Test
vt_Test := pkgl_newsic_ch.fncl_get_tab_centrais_est;

If vt_Test(2)(2) = 'xxx' Then --> here PL/SQL Developer freezes, when mouse enter over "vt_Test(2)(2)" !!
dbms_output.put_line('1');
Else
dbms_output.put_line('2');
End If;
End;
/

Tks !!

[]'s,
Luciana Angeli

Any progress on this issue by the Allroundautomations Team? This bug has made PL/SQL debugger practically useless to me.

Not really. Can you let me know your Oracle Server Operating System?

Hello,

I just tried with PL/SQL Developer 7.1.0 (beta 3), and I still can reproduce this problem.... I'm using Oracle server 9.0.2.7.0. Couldn't you fix it?

Best regards,
Laurent Feltz

i think this somehow related to this one:

topic

It's not only bi dimensional varrays, but normal varrays too . Here a simple test case (with server 9.2.0.7 and pl/sql dev 7.1 beta 3):

As I can read in this post, it seems that it doesn't with oracle databases >= 9.2.0.1

Marco: Could you try this test case using a database like 9.2.0.x? Do you confirm the problem?

CREATE OR REPLACE TYPE "AR_NUM" AS VARRAY(2147483647) OF NUMBER;

CREATE OR REPLACE PROCEDURE test_varray ( par IN ar_num)
IS
BEGIN
IF
par IS NOT NULL
THEN
dbms_output.put_line('<NOT NULL>'); // Breakpoint here -> PL/sql Dev freezes when I try to watch from here the parameter "par"
END IF;
RETURN;
END test_varray;


-- Test script
declare
-- Local variables here
lar ar_num := ar_num (1, 2, 3);
begin
test_varray (par => lar);
end;

Laurent.

Some new info ?

this is still an issue in beta 4

still an issue in beta5 and it's getting worse. now you may even not see the one-dimensional varray values neither by putting the cursor over them nor by copying the variable to the watches list. please, please fix this

If it's getting worse, then maybe you need to shutdown/restart your Oracle Server. We have done nothing in PL/SQL Developer to make it worse.

marco, i believe that you'ne nothing done to make it worse. please try the same script and test at the marks. you will not see any values. tried with server restart, too.

CREATE OR REPLACE TYPE AR_NUM AS VARRAY(2147483647) OF NUMBER
/
CREATE OR REPLACE TYPE AR_AR_NUM AS VARRAY(2147483647) OF AR_NUM
/
CREATE OR REPLACE PROCEDURE DEBUG_TEST
AS
lar ar_num := ar_num();
lar_ar ar_ar_num := ar_ar_num();
ln_dummy PLS_INTEGER;
BEGIN
lar.EXTEND(10);
FOR
i IN 1..10
LOOP
--------------------------> PLACE CURSOR OVER ARRAY HERE
lar(i) := i;
END LOOP;
lar_ar.EXTEND(10);
FOR
i IN 1..10
LOOP
lar_ar(i) := lar;
END LOOP;
FOR
i IN lar_ar.FIRST..lar_ar.LAST
LOOP
FOR
e IN lar_ar(i).FIRST..lar_ar(i).LAST
LOOP
--------------------------> PLACE CURSOR OVER ARRAY HERE
ln_dummy := lar_ar(i)(e);
END LOOP;
END LOOP;
END DEBUG_TEST;
/


oracle server version is 10.2.0.2 and 9.2.0.7. if you need any addtional infos i'll send them all if they help to resolve the problem. pldev becomes unusable for me if this doesn't work.

Marco,

Did you try to reproduce the problem?

Thanks,

Laurent

Just out of interest, what possible use could anyone have for a VARRAY(2147483647) OF NUMBER?

in case of memory usage and performance it doesn't matter how big one declares a varray. 2147483647 is the highest possible size.

I mean, wouldn't it be simpler to use a normal nested table instead? Then you don't have to declare a limit of 2147483647.

VARRAYs are useful for multivalue columns in database tables, however I think the consensus is that using multivalue columns in database tables is not a good idea. Is that what they are being used for here?

It's maybe possible to use nested table instead, but anyway it would be a big change because our Varray ar_num is a type which is used in a lot of packages. We cannot change that now. And as said by sukhadukkham, it doesn't matter how big we declare a varray, 2147483647 is the highest possible size.

Pl/sql dev shouldn't freezes in this case.

For now, I just would like to know if allround can reproduce the problem. If yes, do they see any solution. For now, they didn't answer...

You are right, the debugger should cope. From what I've seen, it has trouble with both varrays and nested table collections.

I often see varrays used in PL/SQL when the more useful nested table type would be a better choice, and I just wondered if there was some reason for it.

yes, we use the varrays for table columns. and so far we didn't experience any problems with that.

any updates on this ?

No updates yet.

this post is 3 years old, and there a still no updates. i can't believe this

Well I think the debugger aught to be able to handle arrays.
It works perfectly well for an array which is a locally declared variable.
But if the array (of same type) is passed as parameter, the whole application freezes when you do "view collection variable". All you can do is shut it down then (just what you want when your debugging, eh?)
It doesn't seem to freeze on hover of the mouse any more (at least in my simple test case).
The least I would expect is the application to continue working. What i really want is of course, to be able to inspect a parameter array just like a locally declared one.
And what's really bad: if i assign the parameter array to the local one, it also hangs on inspection of the local array! So no easy workaround there.

Can Laurent Feltz test this with some reasonable size of array, like 50, 150, 1500, coz if this is related to error in PLSQLDev, then it would hang anyway, but what if PLSQLDev tries to get all those 2.14 milliard stuff from DB?
As I rememeber, PLSQLDev gets only first 1K recs from array, actually...
And myself, I never had a problem of that type - hanging with arrays, but I never use varrays, especially 2.14 miliard ones, nested tables is usually a way to go for me

As suggest by Kirurgs, I tried with a varray with a reasonable size, that is 10! I still have the same problem: pl/sql dev freezes.

I'm using now pl/sql dev. 7.1.1.1339
Oracle release is always 9.2.0.7 (as said in previous messages, it seems that this problem appears with Oracle 9.2.0.1).

Marco, please try to reproduce the problem: please use a database server 9.2.0.x and the following code:

CREATE OR REPLACE TYPE "AR_NUM_TEST" AS VARRAY(10) OF NUMBER;

CREATE OR REPLACE PROCEDURE test_varray ( par IN ar_num_test)
IS
BEGIN
IF
par IS NOT NULL
THEN
dbms_output.put_line('<NOT NULL>'); -- Breakpoint here -> PL/sql Dev freezes when I try to watch from here the parameter "par"
END IF;
RETURN;
END test_varray;


-- Test script
declare
-- Local variables here
lar ar_num_test := ar_num_test (1, 2, 3);
begin
test_varray (par => lar);
end;

[quote]Marco, please try to reproduce the problem: please use a database server 9.2.0.x and the following code[/quote]Sure, we'll check it out, and I will post our findings.

I tested this on 9.2.0.1, where it does indeed cause a hang-up, and on 10.2.0.1, where it works just fine. It looks like a 9.2.0.x specific bug.

Well i'm going to check on our 10.xxx database to see what it does there.

yes it works on our 10.2.0.2.0!
Wow, i'm really glad that bug is gone now.
Now i just hope our development db will be upgraded to 10 soon. (or you guys find out what the 9.2 problem is)