3

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?

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
  • If the application is not supported and is performing unsupported actions, who should fix it? The 3rd party dev or the platform? – Andrew Brennan Aug 22 '16 at 12:35

2 Answers2

4

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.

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
  • 1
    I wouldn't call DBMS_DEBUG obsolete. DBMS_DEBUG_JDWP is better in a few ways, but it requires Java. Which is rather silly for *PL/SQL* debugger, so who can blame tools for not using it. And I've run into problems using that package as well. Sadly, debugging in PL/SQL sucks no matter what you do. I doubt it will ever be fixed. – Jon Heller Mar 12 '14 at 03:21
  • @jonearles Yes, I agree, "obsolete" may be is not right word. What I meant is that as long as Oracle doesn't use it in own's tools there is low or no motivation to keep it up to date and fix bugs timely. They can always say that the matter is third-party tool. Actually when I came across this issue I tried to debug with Toad and SQL Navigator and problem remained the same. So it is definitely something wrong with package itself. I would guess that changes in database internals in modern versions are not fully reflected in DBMS_DEBUG code. – Yaroslav Shabalin Mar 12 '14 at 06:25
  • @jonearles Regarding Java let's keep in mind that Oracle database has native support for Java code and we can mix stored procedures written in both languages. So I would call it an advantage to be able to debug both with a single tool. Another nice feature of DBMS_DEBUG_JDWP is remote debugging. It is sad that tool developers are not pushing this on. – Yaroslav Shabalin Mar 12 '14 at 06:44
-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.

ArtOfWarfare
  • 20,617
  • 19
  • 137
  • 193
Peperre
  • 1
  • 2