Everyone knows that SQL Developer has a PL/SQL debugger – check!

Everyone also knows that it’s only setup for debugging standalone PL/SQL objects like Functions, Procedures, and Packages, right? – NO! SQL Developer can also debug your Stored Java Procedures AND it can debug your standalone PLSQL blocks. These bits of PLSQL which do not live in the database are also known as ‘Anonymous Blocks.’

Anonymous PL/SQL blocks can be submitted to interactive tools such as SQL*Plus and Enterprise Manager, or embedded in an Oracle Precompiler or OCI program. At run time, the program sends these blocks to the Oracle database, where they are compiled and executed.

Here’s an example of something you might want help debugging:

Declare
 x number := 0;
Begin
 Dbms_Output.Put(Sysdate || ' ' || Systimestamp);
 For Stuff In 1..100
 Loop
  Dbms_Output.Put_Line('Stuff is equal to ' || Stuff || '.');
  x := Stuff;
End Loop;
End;
 /

With the power of remote debugging and unshared worksheets, we are going to be able to debug this ANON block!

The trick – we need to create a dummy stored procedure and call it in our ANON block. Then we’re going to create an unshared worksheet and execute the script from there while the SQL Developer session is listening for remote debug connections.

We step through the dummy procedure, and this takes OUT to our calling ANON block. Then we can use watches, breakpoints, and all that fancy debugger stuff!

First things first, create this dummy procedure -

create or replace
procedure do_nothing is
begin
 null;
end;

Then mouse-right-click on your Connection and select ‘Remote Debug.’ For an in-depth post on how to use the remote debugger, check out Barry’s excellent post on the subject.

Open an unshared worksheet using Ctrl+Shift+N. This gives us a dedicated connection for our worksheet and any scripts or commands executed in it.

Paste in your ANON block you want to debug.

Add in a call to the dummy procedure above to the first line of your BEGIN block like so

Begin
 do_nothing();
...

Then we need to setup the machine for remote debug for the session we have listening – basically we connect to SQL Developer. You can do that via a Environment Variable, or you can just add this line to your script -

CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'localhost', '4000' );

Where ‘localhost’ is the machine where SQL Developer is running and ’4000′ is the port you started the debug listener on.

Ok, with that all set, now just RUN the script.

Once the PL/SQL call is made, the debugger will be invoked. You’ll end up in the DO_NOTHING() object.

Debugging an ANON block from SQL Developer is possible!

If you step out to the ANON block, we’ll end up in the script that’s used to call the procedure – which is the script you want to debug.

The Anonymous Block is opened in a new SQL Dev page

You can now step through the block, using watches and breakpoints as expected.

I’m guessing your scripts are going to be a bit more complicated than mine, but this serves as a decent example to get you started.

Here’s a screenshot of a watch and breakpoint defined in the anon block being debugged:

Breakpoints, watches, and callstacks - oh my!

For giggles, I created a breakpoint with a passcount of 90 for the FOR LOOP to see if it works. And of course it does

You Might Also Enjoy

• Using Pass Counts to Turbo Charge Your PL/SQL Breakpoints
• SQL Developer Tip: Viewing REFCURSOR Output
• The PL/SQL Debugger Strikes Back: Episode V
• Debugging PL/SQL with SQL Developer: Episode IV
• How to find dependent objects in your PL/SQL Programs using SQL Developer