Debuger with Oracle11 bugged

[AdamG]

Hello,

I noticed some problems with debugging when connected to Oracle11. When program unit which you debug step by step throows an exception PL/SQL Developer hangs.
For test I created a procedure with sthg like dbms_output.put_line(5/0); inside and exception didn't appear. Instead Developer went into some infinite loop which could stop only holding down Shift-Esc and then I got many ORA-01476.

I use:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Developer Version 7.1.4.1389
Windows XP sp2

I don't have any such problems with my other Oracle servers from 9i to 10g.

Any solution/workaround?

Adam.

 

[Marco Kalter]

We will check it out.

 

[AdamG]

Hi Marco,

Did you manage to reproduce it?

 

[Marco Kalter]

Not yet. I will keep you posted.

 

[joel]

We are a debugger issue with 11g RAC as well -- we are unable to step into the program.

 

[Gustavo]

Have you enabled debug information for your program? This is mandatory in this database version, if you want to step into the code.

 

[Willem]

Having the same problem as Adam with Oracle 11g 32bit on Windows and PL/Sql Developer Version 7.1.4.1389.

Stepping through a debug window does not display an exception window and the IDE seems to hang. Kill the DB session and repeatedly press ctrl-esc in the IDE to display the exception window.

Could not find a workaround and had to re-install 10g for my development environment.

 

[Marco Kalter]

We have not been able to reproduce this, all seems to work fine on 11g. It may be a configuration issue. For example a shared pool size that is too small.

 

[joel]

I think I find the issue in our shop... when right clicking on the package in question, the check box appeared next to the "Add Debug Information" line, so I didn't think I needed to "re-add" it. However, without repeating the "Add Debug Information" step, we were hung up as indicated in this thread.

Problem is resolved from our perspective, but perhaps there could be some investigation into why this check is showing enabled when there hasn't been any debug information added.

Thanks!

 

[Marco Kalter]

PL/SQL Developer reads this information from the sys.all_probe_objects view. For example:

select debuginfo
  from sys.all_probe_objects
 where owner       = 'SCOTT'
   and object_name = 'EMPLOYEE'
   and object_type = 'PACKAGE BODY'

This returns 'T' if the package body has debug info, and 'F' if not.

 

[SunDog]

Are you sure?

After recompiling a package body, the "Add Debug Information" checkbox remains checked even though the above query returns "F". Reconnecting does not change this.

 

[Marco Kalter]

Yes, I'm sure. Note however that:

1. The package specification and body are 2 different object types.
2. The debug information status is cached in the Object Browser, so a refresh may be required before you see a change.

 

[SunDog]

Sorry if I'm not understanding the intended behaviour correctly, Marco. But I'm just not seeing it. I created a package and body, then added debug information. There was a checkmark for "Add Debug Information", as expected. I then recompiled the package body only (using CREATE OR REPLACE again, not "Recompile") which removed the debug info. The query you provided returns F, again as expected.

So I restarted PLSQL Developer completely, which would seem to preclude there being a cached value in the Object Browser. But "Add Debug Information" is still checked. I was being a bit facetious when I asked if you were sure. But I'm now wondering if that, in fact, is the case?

 

[SunDog]

Looking for a response......

thanks.

 

[Marco Kalter]

I can only imagine that you are looking at the spec after recompiling the body or vice versa. Otherwise I have no explanation at the moment. Everything works as expected when I try it.

 

[Marco Kalter]

You could also check if there are no spaces or other characters in the debuginfo field. Execute the following query:

select debuginfo, dump(debuginfo)
  from sys.all_probe_objects
 where owner       = 'SCOTT'
   and object_name = 'EMPLOYEE'
   and object_type = 'PACKAGE BODY'

The dump() function will indicate if the value is exactly 'F' or 'T'.

 

[SunDog]

Sorry, I'm not seeing it. That query returns exactly "T" for the package spec and "F" for the package body, yet right-clicking on the package name shows a checkmark for Debug Info. Is there somewhere that I can check that the query is correct or is it internal?

 

[Marco Kalter]

Sorry, I'm not seeing it. That query returns exactly "T" for the package spec and "F" for the package body, yet right-clicking on the package name shows a checkmark for Debug Info.

This would be correct. The package specification is compiled with debug information, and the package body is not. Right-clicking on the Package should show a check mark, and right-clicking on the Package body should not show a check mark.

 

[SunDog]

That makes sense now. Since having debug info on package spec and not the body isn't really all that helpful, may I suggest that the query be something like:

select debuginfo
  from sys.all_probe_objects
 where owner       = '#oowner'
   and object_name = '#oname'
   and object_type = DECODE('#otype','PACKAGE','PACKAGE BODY','#otype')

so that when the object type is a package, the checkbox shows the state of debug information for the body? (Although, I guess I could create my own BE to do this myself.)

 

[Marco Kalter]

I have added this to the list of enhancement requests.