V8 bug: breaking a debug on 11g database behaves like step out

[Claus Pedersen]

I am debugging a method inside a database package in 11g version 11.1.0.7.0. When the debugger stays inside the initial package, I can break (stop) the debugger almost every time, but when the debugger calls into a method in another package, I can not break the debugger immediately. Instead, it behaves like step out (Ctrl-T), i.e. each time I press the break button, the debugger goes to a line in a method at a call level 'above' the current line.

As I recall, this was also a problem in one of the early 8a versions, but that time with a 10g database.

Could this behaviour be corrected with a high priority, as this stops me from debugging effectively on a 11g database.

 

[Claus Pedersen]

BTW: if you try to close the test window, and select Terminate in the following dialog, you can watch PLD go to all the 'breakpoints' before closing the window.

 

[Marco Kalter]

I checked this on 11.1.0.6 and it works fine. I will try this on 11.1.0.7 as well.

 

[Claus Pedersen]

I am getting an Oracle error now.

I make a test script where I test a method in a package (P1) containing approx. 2100 lines of code. This method in turn calls a method in another package (P2) containing approx. 6400 lines of code.

I make a breakpoint in the method in P2. When I run the test script to the breakpoint and break the debugger, I get the error:

ORA-00604: error occurred at recursive SQL level 1
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.PBREAK", line 1080
ORA-06512: at "SYS.PBSDE", line 201
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 1

File debug.sql will be sent to support.

 

[Claus Pedersen]

A simple test script:

BEGIN
  :num1 := to_number ('3');
  :num2 := to_number ('4');
END;

Enter debug mode. The cursor is at the first line. Pressing the Break button, the cursor moves to the second line. Pressing Break again breaks the execution.

Performing the same steps again (in same test window), this time results in a delay of some seconds (depending on the connection to the database) and showing of message "ORA-01013: user requested cancel of current operation"

Performing the same operation again results in the first described behaviour (without message) and the behaviour shifts back and forth.

 

[Claus Pedersen]

Any news on this? It has become quite an annoying business to debug code on an 11g base.

 

[Marco Kalter]

This is still under investigation.

 

[Jules]

I'm using 8.0.1.1502 and am getting slightly different issue around debugging. As soon as an error is raised developer hangs. Break can sometimes sort it out but sometimes just need to kill the windows process. This makes debugging almost impossible.

declare
PROCESSING_E exception;
pragma exception_init ( PROCESSING_E, -20099 );
begin
raise PROCESSING_E;
end;

Running the above is fine but if you debug through by stepping over every line after stepping over the raise statement developer hangs for a few seconds then seems to return - run, step into, etc. buttons become enabled but no error is raised. The only way to progress is hit break a number of times (and the number does seem to vary). At some point a ORA-01013 error is returned - after hitting OK the error is raised five or six times. In a more complex program often no ORA error is returned and therefore its impossible to get control back.

 

[Claus Pedersen]

Thanks Jules for a very nice example.

I have given it to our DBA, who has run it on Toad, but it did not hang up. But on the other hand, the debugger in Toad does not singlestep the code like PL/SQL Developer does. It seems more like like it sets a breakpoint and run to that breakpoint. But anyway, the ORA-20099 always comes up on Toad.

Can anyone try to reproduce this with Oracle SQL Developer?

Marco, how are the investigations comming?

 

[Claus Pedersen]

I have tried today with Oracle SQL Developer. This debugger works (it does not hang up).

I have made the block from Jules into a procedure TESTER and debugged it in Oracle SQL Developer.

The output from the debugger is as follows:

Connecting to the database scott11.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( <IP>, '1893' )
Debugger accepted connection from database on port 1893.
Exception breakpoint occurred at line 8 of TESTER.pls.
$Oracle.EXCEPTION_ORA_20099:
ORA-20099:
ORA-06512: at "SCOTT.TESTER", line 7
ORA-06512: at line 2
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Process exited.
Disconnecting from the database scott11.
Debugger disconnected from database.

Maybe someone can figure out, what Oracle does differently from PL/SQL Developer.

Marco, you must really try to find a solution to this. My company are installing more and more 11g databases for our customers and if the developers/support teams are not able to debug properly without loosing data and potentially locking the customers tables because of a hanging debug session, we may have to be forced to look at other development tools for debugging porposes

 

[Jeff81]

I also have the same problem as Jules. Very annoying.

 

[dragonsoft]

Me to, Sometimes when i break execution i have to step like said above and sometimes not. but it usually shows the script tab and tries to raise a dialog window that dont show upp (you can hear the sound), the test window is in a state that is running so logoff och break is impossible. only task manager can help me...

This has started to happen alot.
Shall i upload support info?
Using
Home: OraOdac11g_home1
DLL: C:\app\???\product\11.1.0\client_1\oci.dll
OCI: version 11.1
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

 

[Maxnet]

I also have the same problem

 

[Claus Pedersen]

I have tried the new version 8.0.4.1514 and now it is much better. When the debug is broken (shift-Esc), the system no longer hangs. A big thumbs up for the developers!

But (some people are never satisfied ): the execution is not stopped when the user breaks the debugger, the program continues to the end. Besides the potential great amount of time before the program comes to an end, there is a problem if the program communicates with external connections. For example a webservice, a mail-server or other non-transactional services, because this will cause for example a webservice to be called each time a program is tested, and test data may be destroyed because it is executed on a third party system.

A small test to verify this behaviour can be performed by executing a small piece of test code like the following:

BEGIN
  NULL;
  dbms_output.put_line (1/0);
END;

Enter debug mode and break the debugger at the line with NULL. The error "ORA-01476: divisor is equal to zero" is still shown, even if the debugger is broken before this line.

On Oracle 10g, the debugger is correctly broken as expected with the message "ORA-06543: PL/SQL: execution error - execution aborted"

Can this error be fixed for Oracle 11g as soon as possible?
Thanks...

 

[Claus Pedersen]

Will this be fixed for 9.0?

 

[Marco Kalter]

We have not yet been able to reproduce this.

 

[Claus Pedersen]

Hello Marco.

Which steps have you performed to reproduce?
Can you reproduce with the example code I reported in this topic on 19. June 2010?
The posters Jules, Jeff81, dragonsoft, Maxnet, datadeconline are all reporting the same issue, so it should not be too hard to reproduce.

It is critically important that this bug is locallised and fixed.

In our company we experience this problem on a daily basis, both by our inhouse developers and when we occasionally have to test code at our customers' databases.

The major problems are:
1) Sometimes PL/SQL developer hangs when a debug session is broken (Shift-Esc). When no database updates are done yet when stepping through the code, a break can usually be performed without problem, but when database updates are done, sometimes the debugger just hangs and sometimes the debugger asks if I want to break. I answer Yes, but the session is still in a strange mode where the red "execute lightning" is gone, but the green Run arrrow is still active in the test window. In this case, PL/SQL Developer can not be closed, because the debug session is reported as still running. The only way to quit PL/SQL Developer now is to use the task manager. When the debugger hangs after a database update, the Oracle session is still active with table locks etc. This session can be hard to kill and may at it's worst require a database restart to release the table locks by this session. So for customers where we have to test something, for instance to reproduce an error, this can be quite interfering for their work.

2) The debugger does not break execution when aquired, but continues the execution of code. This means, that if a method has table modifications with explicit commits etc., the data is changed. In the case of running test scripts in order to reproduce data errors etc. at customers, this can have adverse effects, as data can have been sent to other systems etc.

Please, please, please make an attempt to solve this problem.

If it can be of any help to you, I would be glad to participate in a shared dekstop session (for instance via Webex) where I can demonstrate the problem, and you or another developer/supporter can participate also. Please let me know, when this is convenient for you.

 

[datadeconline]

Hi Marco,

I follow all notes on before comment (Claus Pedersen), if I help to you please tell me. This is very important for us and for all PL/SQL developers.

Regards
PGuti

 

[Claus Pedersen]

Hello, have you been able to reproduce?

It is a major problem.

 

[Claus Pedersen]

Hello Marco, how is progress on this issue? My offer to participate in for instance a TeamViewer session or similar in order to illustrate the problems is still open ...

Not that I am counting, but is has been 774 days since the first post in this thread ...

 

[Marco Kalter]

Unfortunately we have not been able to reproduce this.

 

[Claus Pedersen]

What steps have you performed to reproduce?

I am running:
Windows XP Professional 5.1 Build 2600 (Service Pack 3)
PLD Version 9.0.2.1635
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
sql*net client version 8.0

Please try with these settings, am I getting this error on three different physical machines, so it must be reproducible for you ...

Again, why don't we connect and demonstrate via a TeamViewer session?

 

[abenas]

Same problems here.

I was blaming Oracle 11g, but seens like a bug in plsql developer. I've read the entire forum for an answer. This is really annoying.

PL/SQL Dev. version: 9.0.4.1644 01.493953
Oracle Client: 11.2.0 Windows XP 32bit
Oracle Server: 11.2.0

 

[EJB]

I have exactly the same problem on my setup here. I'm running PL/SQL Developer 9.0.3.1641 connecting to Oracle 11.1.0.7.0. My PC runs Windows 7 64 bit but I have the 32 bit Oracle client installed.

Has there been any progress on this? It's very frustrating to have to "step out" all the way to the end of the program when you just want to stop execution.

Thanks

 

[Claus Pedersen]

What is the ETA of this bug fix? Have you been able to reproduce? It is an increasing problem, as more and more of my development is on a 11g database.

 

[Worker]

I can easily reproduce this with Claus' earlier testcase:

BEGIN
  NULL;
  dbms_output.put_line (1/0);
END;

Oracle 10.2.0 client, 11.1.0.7.0 database.

 

[Claus Pedersen]

Dear Marco

How are the efforts going to shed some light on this problem?

As you can see from the posts above, it is a commonly known problem amongst developers in the community.

Please, please, please give us a little to work with (a test session, a description of the tests you have performed, or similar).

 

[Marco Kalter]

It's a little bit too complex to describe here. We are looking into it though.

 

[Claus Pedersen]

Thanks.
Please give it highest possible priority, just below breathing, eating and going to the toilet :grin:

 

[Claus Pedersen]

Any news?

It is a source of daily annoyance that a debug session can not be broken at will and the only solution to stop the debugger is to kill the PLD program.

It has nearly been three years now since the my first post. It would be nice for us all to be able to tick this one off as solved ;-)