Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
Can you let me know your PL/SQL Developer version? Can you also let me know the definition of the array?
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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";
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
I the array defined within a package or through a "create or replace object" statement?
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
Works fine for me. What is your server version?
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
Where you able to reproduce the bug yet?
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
Not yet. It's quite odd that you have had this problem on various platforms / versions, and that we cannot reproduce it.
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
OK, one case it happens is, when the array is a parameter.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jul 2004
Posts: 5 CA, US
Member
|
Member
Joined: Jul 2004
Posts: 5 CA, US |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jul 2004
Posts: 5 CA, US
Member
|
Member
Joined: Jul 2004
Posts: 5 CA, US |
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"?
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
No clue yet. What is your server operating system?
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 2004
Posts: 2
Member
|
Member
Joined: Aug 2004
Posts: 2 |
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
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jul 2004
Posts: 5 CA, US
Member
|
Member
Joined: Jul 2004
Posts: 5 CA, US |
Any progress on this issue by the Allroundautomations Team? This bug has made PL/SQL debugger practically useless to me.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
Not really. Can you let me know your Oracle Server Operating System?
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Mar 2007
Posts: 12 Swiss
Member
|
Member
Joined: Mar 2007
Posts: 12 Swiss |
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
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
i think this somehow related to this one: topic
|
|
|
Re: Debugger Hangs on Array
|
Joined: Mar 2007
Posts: 12 Swiss
Member
|
Member
Joined: Mar 2007
Posts: 12 Swiss |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Mar 2007
Posts: 12 Swiss
Member
|
Member
Joined: Mar 2007
Posts: 12 Swiss |
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
this is still an issue in beta 4
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
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
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
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 Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Mar 2007
Posts: 12 Swiss
Member
|
Member
Joined: Mar 2007
Posts: 12 Swiss |
Marco,
Did you try to reproduce the problem?
Thanks,
Laurent
|
|
|
Re: Debugger Hangs on Array
|
Joined: Sep 2003
Posts: 389 London, UK
Member
|
Member
Joined: Sep 2003
Posts: 389 London, UK |
Just out of interest, what possible use could anyone have for a VARRAY(2147483647) OF NUMBER?
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
in case of memory usage and performance it doesn't matter how big one declares a varray. 2147483647 is the highest possible size.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Sep 2003
Posts: 389 London, UK
Member
|
Member
Joined: Sep 2003
Posts: 389 London, UK |
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?
|
|
|
Re: Debugger Hangs on Array
|
Joined: Mar 2007
Posts: 12 Swiss
Member
|
Member
Joined: Mar 2007
Posts: 12 Swiss |
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...
|
|
|
Re: Debugger Hangs on Array
|
Joined: Sep 2003
Posts: 389 London, UK
Member
|
Member
Joined: Sep 2003
Posts: 389 London, UK |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
yes, we use the varrays for table columns. and so far we didn't experience any problems with that.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2004
Posts: 105
Member
|
Member
Joined: Jun 2004
Posts: 105 |
this post is 3 years old, and there a still no updates. i can't believe this
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Feb 2005
Posts: 59 Latvia
Member
|
Member
Joined: Feb 2005
Posts: 59 Latvia |
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
regards Kirurgs
|
|
|
Re: Debugger Hangs on Array
|
Joined: Mar 2007
Posts: 12 Swiss
Member
|
Member
Joined: Mar 2007
Posts: 12 Swiss |
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;
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
[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.
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Aug 1999
Posts: 22,487
Member
|
Member
Joined: Aug 1999
Posts: 22,487 |
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.
Marco Kalter Allround Automations
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
Well i'm going to check on our 10.xxx database to see what it does there.
|
|
|
Re: Debugger Hangs on Array
|
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland
Member
|
OP
Member
Joined: Jun 2003
Posts: 52 Lenzburg, Switzerland |
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)
|
|
|
|
|