Using SQL Developer to Debug your Anonymous PL/SQL Blocks

thatjeffsmith SQL Developer 18 Comments

Tell Others About This Story:

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.

Or, Using the Environment Variable
ORA_DEBUG_JDWP=host=mypc;port= 1234

The obvious advantage here is that none of your source code needs altered.

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 🙂

Tell Others About This Story:

Comments 18

  1. Unfortunately I didn’t get this to work.

    A warning to those who create an environment variable ORA_DEBUG_JDWP: the presence of this environment variable appears to cause (silent) failures of attempts to install Oracle XE 11g.

  2. Hi Jeff,

    I’m trying to remote debug a stored procedure and the debugger just blows past all of my breakpoints. The connection is fine – I can see the debugger connect and then disconnect, but the breakpoint I have set in between the connect and disconnect commands (using dbms_debug_jdwp) is completely ignored.

    The request is coming from a web application. That application uses a single web user to handle all transactions from the application to the db. The privileges for that user are limited as they should be. When I have the remote debugger running in sqldeveloper, I’m logged in as myself so that I can actually see and edit the procedure (which is owned by another higher-privileged user, other than myself).

    Is the debugger ignoring the breakpoints I’m setting because the request is coming in as the web user? How can I cause any breakpoint that I set to be hit regardless of who is making the request?

    Thanks,
    Mehr

    1. thatjeffsmith Post
      Author

      Two things:

      1. make sure the procedure is compiled for debug
      2. make sure the breakpoint is set on an executable line of code

      In either of these cases, the breakpoints are ignored.

      When you debug it locally, is said breakpoint honored?

      1. Hi Jeff,

        Well, I got things working and am honestly not sure how. It may have been that my original breakpoint was not on an executable line of code, as you suggested, but the line of code I had chosen originally was this:

        queryCtx := DBMS_XMLQuery.newContext
        (‘select * from bigdata1.data_hash_view where dbid = ‘||ds_id);

        Is there any reason why that line of code wouldn’t catch a breakpoint?

        Anyway, thanks for the help.

        Best,
        Mehr

  3. I have found the easiest way to do anon code debugging is simply:

    1) Go into: Preferences -> Debugger
    2) Under ‘Start Debugging Option’ choose ‘Step Into’
    3) Open the script you want to anonymously debug
    4) Right click and choose ‘Debug’ from the popup context menu (CTRL+SHIFT+F10)

    Voila, now the debugger should be halted at the 1st line of your code. You can now insert breakpoints anywhere and use normal debugging stuff.

    —–

    Now my only question is, how to view the real time debugging contents of PL/SQL collections as the individual records are obfuscated.

  4. The article is really excellent. However I am just unable to invoke or insert a breakpoint in the above anonymous procedure. The version of SQL Developer is: Version 4.0.3.16

    I have followed the steps listed by you Jeff and also Barry McGillin’s article yet no success. Is there any way to invoke the breakpoint and have it inserted into the anonymoys block as you have depicted above (pinkish color or to that matter invoking a watch window). Please gelp.

    Sandeep

  5. I had to add ALTER SESSION SET PLSQL_DEBUG=TRUE, otherwise I couldn’t step into anonymous block.

    Here is the full content of my unshared sql worksheet

    ALTER SESSION SET PLSQL_DEBUG=TRUE
    CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘localhost’, ‘4000’ );
    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;
    /

  6. Your comment is awaiting moderation.

    Hi, could you help please, I’m having some trouble.. let me try to explain..

    I could start remote debuging with sqldeveloper calling it from a anonymous block from my java program, it starts perfectly debugging the do_nothing procedure from sqldeveloper and I can see on the stack two items (do_nothing an AnonymousBlock), the debugger steps into others functions declared in my anonymous block perfectly but… I cant debug my anonymous block, and when I double click its from stack’s list, it does not show my code..
    where I am failing?.

    sorry I forgot to mark to receive someone replies in my last post

  7. Great article. Just like to add a small information. Before starting the first debugging run, add a breakpoint into the procedure “do_nothing”. On my system (SQL-Developer 3.2.2) this seemed to be another way to get the anonymous_block window. And from there adding further breakpoints was obvious.

  8. There is “debug” context menu item in SQL worksheet. However, there is no way to set breakpoints in SQLWorksheet. If you have Preferences->Debug->”Step Into” then the debuggin process would open PL/SQL editor for anonymous block with execution stopped at the very first line, where you can set breakpoints and do normal debugging.

    1. thatjeffsmith Post
      Author
  9. thatjeffsmith Post
    Author
    1. Hey Jeff,

      Well the good news is a restart of SQL Developer seemed to fix the problem with the popup. I executed the anon script in an unshared worksheet with the dbms_debug call. I tried to make the dbms_debug call in another unshared worksheet as well but kept getting errors. I was most interested in running anon scripts the most from this article, as of right now I start a debug on a procedure and replace the autogenerated script with my anonymous script.

      I guess a picture might help:

      http://i18.photobucket.com/albums/b116/dtdono0/Oracle/oracle_debug001.png

      1. thatjeffsmith Post
        Author

        You only use localhost if you’re database is on the same machine as SQL Developer. Replace localhost with the IP or network name of the machine where you have SQL Developer installed.

  10. Jeff, your article seems a little unclear.

    – I pasted your anon script,
    – created the dummy proc
    – right clicked on the db connection, clicked remote debug…no window for pops up
    – and obviously the DBMS_DEBUG_JDWP.CONNECT_TCP fails afterwards.

    My sandbox environment is a Win7 host running oracle 11g on a windows server 2008 guest VM via VirtualBox. SQL Developer is v3.1.07 which I am running from the host. I don’t understand why the window does not appear for remote debug though.

    Maybe I should try running SQL Developer on the VM to see if that is causing the issue.

Leave a Reply

Your email address will not be published. Required fields are marked *