PL/SQL developer: debugging hangs on certain code

Question

I have noticed that when debugging code with PL/SQL Developer, debugger hangs on stepping over certain code. The first guess was to blame that line of code, but further investigation detected nothing suspicious in it. Just simple operations inside stored procedure or call to system functions. There is clearly no reasons to hang.

Querying system views led me to finding that current SQL_CODE of debugger session is:

declare ret binary_integer; begin ret := PBSDE.DEBUG_LOOP; end;

After some time PL/SQL Developer throws an error:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I have found related Note on Oracle Support site with exact same symptoms (Doc ID 1074885.1). But solution suggested there is either funny, or pathetic. I know that information on Oracle Support is not allowable to share, but such a thing deserves to break the rules. I will take the liberty of citing the solution part in full:

Do not run PL/SQL Developer in debug mode against an Oracle database.

Yes, that's it. So what options do I have?

Answer 1

Seems that you are lucky enough to come across the combination of Oracle database internal bug (technically not a bug, call it "feature" if you will) and SQL development tool you use. There are several threads on Allround Automations forum regarding this issue (i.e. this one, you can search the others by keyword PBSDE.DEBUG_LOOP), but no clear solution suggested. As usually happens Oracle blames the tool, Allround Automations blames the database.

In few words PL/SQL Developer uses system package DBMS_DEBUG and it's routines for debug purposes. The package itself is kind of obsolete (introduced in 8i version), but many IDEs still use it. The reason of hang lies somewhere inside this package and PL/SQL Developer has nothing to do with it (except not to use the package at all). Ideally Oracle should fix it but this is not likely to happen because there is more modern alternative package called DBMS_DEBUG_JDWP which serves the same purposes. Seems that the only tools that support debugging with this package are Oracle's own SQL Developer and JDeveloper (that's why there is low effort to fix the bug in obsolete code from Oracle's side).

So the alternative is to use SQL Developer or JDeveloper for debugging until PL/SQL Developer begin using DBMS_DEBUG_JDWP or Oracle fix DBMS_DEBUG. There is detailed step-by-step guide on that topic in Oracle documentation.

Answer 2

Occasionally, SQL Navigator loses references and doesn't debug some procedures. To correct this, you should compile the procedure where you want to debug. This doesn't mean the procedure itself is invalid.