The PL/SQL Debugger Strikes Back: Episode V

thatjeffsmith SQL Developer 31 Comments

Tell Others About This Story:

The DBA panel in SQL Developer displays sys privs

I’ve already discussed how the Rebel scum go about figuring out what’s wrong with their code. Now I want to get into the nitty-gritty details of how to start a debug session in SQL Developer. There are some prerequisites, but I promise it won’t take but a few moments to setup.

Ensure that

  • you have been granted the DEBUG CONNECT SESSION priv
  • you have EXECUTE privs on DBMS_DEBUG_JDWP
  • you have EXECUTE privs on the object you want to debug

Once you have done ALL of that, you only have one more thing before you actually start debugging. It’s time to COMPILE our PL/SQL object WITH DEBUG. If you do not perform this step, you may find that your breakpoints aren’t honored and that your watches don’t actually watch anything. To compile with debug information, just do this:

Click on the dropdown control next to the compile (gears) button

Oh, I almost forgot to remind you – you have a test/dev environment, right?

You want to mind having any objects compiled for debug in your production environments. It adds a bit of nasty overhead and will impact performance. It won’t show up when you are running it, but if your app is calling it a few hundred times a minute, it will stack up on you in a hurry. So, if you see the little green widget next to any of your PL/SQL objects in production, just be sure to compile it again to remove it.

Alright, you have all the privs, you have compiled for debug, and you’re in a nice, safe, & cozy place to debug (not PROD! Ok maybe sometimes PROD). You are just one or two clicks away.

There is a ‘debug’ button you could hit, but don’t do that yet!

The debug button will start a debug session

Let’s say you DO hit that button. You might be a bit disappointed. You see, the default behavior of the debugger is to run until it hits a breakpoint or an exception. But, we haven’t set any breakpoints yet! If you do this, it will indeed start a debug session and run your program. But, it will also finish and you won’t really see much of anything except for the normal output of your program.

So instead, I want you to open the Preferences dialog under the Tools menu, and go to the Debugger page. Find the ‘Start debugging option’, and change it to ‘Step Over.’

Once you do that, you’ll find that the program is sitting at the first line of executable code waiting for you to take over.

Just one more thing

Before the debug session begins, you’ll see a dialog that looks EXACTLY like the ‘Run PL/SQL’ one. You will need to input any required IN parameters. In addition if you want to debug a package member, be sure you actually select the right target.

And that’s about it for STARTING your debug session. There’s actually another way to start a debug session from OUTSIDE of SQL Developer, but I’m going to save that for another episode. I may even do it in IMAX 3D so I can start to recoup some of these production costs…step over versus into, watches, breakpoints, call stack, etc will be covered in the upcoming post as well.

Tell Others About This Story:

Comments 31

  1. Could you help me here?

    ORA-01031: insufficient privileges
    ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
    ORA-06512: at line 1
    This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
    Process exited.

    I’ve given all 3 privileges but still i get to see above error.

    grant debug connect session to ;

    grant debug any procedure to ;

    GRANT EXECUTE ON DBMS_DEBUG_JDWP TO ;

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        sorry for the late reply. I could fix this and able to debug now. It’s McAfee firewall created lot of problems and finally turned that off to make it work.

      2. thatjeffsmith Post
        Author
  2. Hello Jeff

    In our company, we are working with SQL Developer 4.1, and some users wants to use a Debugger. The case is: they have a package and they have 1 cursor (for icur in cursor loop), this cursor have 50.560 rows, so, some users want to go at row 1500 because in this row the icur.ID=30500 and the icur.NAME=’ROCIO’. But we cant to do that. Please tell us, how can do it.

    1. thatjeffsmith Post
      Author
      1. Jeff

        This is my first step that you says. I put the break poing (in) cursor and (after) cursor on another variable, but never stop when I use modify value, or inspector or watched.

        I need the debug stop when cursor arrived to row 1500. In PL/SQL Developer, I put a value example: icur.ID=30500 and the cursor stop in this value and I continue debugging.

        I hope your answer

        I hope your answer

        The URL, I see: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/plsql_debug/plsql_debug_otn.htm

        AND

        http://www.thatjeffsmith.com/archive/2011/12/debugging-plsql-with-sql-developer-episode-iv/

      2. Jeff

        This is my first step that you says. I put the break poing (in) cursor and (after) cursor on another variable, but never stop when I use modify value, or inspector or watched.

        I need the debug stop when cursor arrived to row 1500. In PL/SQL Developer, I put a value example: icur.ID=30500 and the cursor stop in this value and I continue debugging.

        I hope your answer

      3. thatjeffsmith Post
        Author

        show me your breakpoint or watch with break condition

        there was a bug where the variables were being treated as case-sensitive so you might be getting tripped up by that

  3. Hi Jeff,
    When I try to pick a target procedure to debug the dialog only shows one possible option. It doesn’t show the proc I wanna debug. When I right click on the proc I wanna debug the only option is ‘Order members by’.

    The package spec is:
    procedure main;
    function get_max_eff_term_code (the_table varchar2, sbgi_code varchar2, mysubject varchar2, coursenum varchar2, myterm varchar2) return varchar2;
    PRAGMA RESTRICT_REFERENCES (get_max_eff_term_code, WNDS);

    The only target option is get_max_eff_term_code.
    The target I want to debug is main().

    Any hints?
    Thanks

    1. thatjeffsmith Post
      Author

      I dunno except that it works for me?

      Check the View > Log > statements panel. We run this to get the list of package members for debugging:

      SELECT DISTINCT A.OBJECT_NAME, A.OVERLOAD, A.OBJECT_ID
      FROM SYS.Dba_ARGUMENTS A, SYS.Dba_OBJECTS B
      WHERE A.OBJECT_ID = B.OBJECT_ID AND
      B.OWNER = 😡 AND B.OBJECT_NAME = :y AND B.OBJECT_TYPE = :z

  4. Hi Jeff,

    I’d like to understand the mechanics of the debugger getting launched but breakpoints not being hit so that I can solve my problem.

    Packages are compiled with debug. I believe that all of the permissions needed are granted.

    I am experiencing similar behavior to this: https://community.oracle.com/thread/2498760, regardless of if I use the same user, but different session to:
    a) log in with sql developer for the listener
    b) executes the task on the db.

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author
      2. Sorry, both have been done, but the debugger just launches and then ends. The points are never hit.

        I have not tested the exact same breakpoints, as the hidden state is too large to generate by itself, but I have a simple example where I can execute the ‘standalone debug’ (the little bug icon) by itself and the breakpoint is hit.

      3. thatjeffsmith Post
        Author

        >>Comment: Sorry, both have been done, but the debugger just launches and then ends
        Tools > Preferences > Debugger > Start Debugging Option: Set Over

        Now debug again, and step through your code.

        My guess is that your breakpoint is in a code section that’s never actually reached.

      4. >>>>
        >>Comment: Sorry, both have been done, but the debugger just launches and then ends
        Tools > Preferences > Debugger > Start Debugging Option: Set Over

        Now debug again, and step through your code.

        My guess is that your breakpoint is in a code section that’s never actually reached.
        << launch debugger
        breakpoint > assign variable
        breakpoint > function call
        breakpoint > loop

        There are no conditions inbetween.

      5. thatjeffsmith Post
        Author

        Then something is borked. You should see the anon block we use to start the debug session, where you would then step into the actual unit to be debugged.

      6. Hmm, I wrote a nice long reply. Somehow it got destroyed. Essentially, I call the debugger and have breakpoints set on the debugger call and the next X lines, there are no conditions which would cause something to not get executed. For example, I had a call to the debugger in a loop and a disconnect at the end of the loop. I set breakpoints on everything in the loop and the disconnect. The system happily connected and disconnected the debugger, but never hit a breakpoint.

      7. Re: the anon block. The system has so much hidden state that the anonymous block is just not feasible. It would be great just to call the function by itself.

      8. Headed home. Thanks for the quick replies. I think that you are doing a great job for Oracle in general.

      9. Sorry, both have been done, but the debugger just launches and then ends. The points are never hit.

      10. thatjeffsmith Post
        Author
      11. Yes, it is on the line after launching the debugger. After a few hours, I added at least 10 breakpoints to be sure that they are not on something that doesn’t work with a break. I have them on a loop, variable assignment, function call etc.

  5. How this can be Run/Debug from SQL Developer.
    create or replace
    PACKAGE test_pkg_emp AS
    TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    TYPE t_varchar IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
    FUNCTION inst_emp(P_EMPNO IN T_NUMBER,
    P_ENAME IN T_VARCHAR DEFAULT CAST(NULL AS T_VARCHAR) ) RETURN NUMBER;
    END test_pkg_emp;
    create or replace
    PACKAGE BODY test_pkg_emp AS
    FUNCTION inst_emp(p_empno IN T_NUMBER,
    p_ename IN T_VARCHAR DEFAULT CAST(NULL AS T_VARCHAR)
    ) RETURN NUMBER
    AS
    v_empno number;
    BEGIN
    FOR i IN P_EMPNO.FIRST..P_EMPNO.LAST Loop
    insert into emp(empno,ename) values (p_empno(i),p_ename(i));
    END LOOP;
    SELECT COUNT(*) into v_empno FROM emp;
    RETURN v_empno;
    END inst_emp;
    END test_pkg_emp;

    From SQLPLUS this can be called as
    DECLARE
    v_eno test_pkg_emp.t_number;
    v_ename test_pkg_emp.t_varchar;
    V_No NUMBER;
    BEGIN
    v_eno(1):=9890;
    v_eno(2):=9891;
    v_ename(1):=’test1′;
    v_ename(2):=’test2′;
    v_no:=test_pkg_emp.inst_emp(v_eno,v_ename);
    END;
    /

  6. Hi Jeff,

    just experimented with the plsql debugger in sqldev. Really cool stuff!
    There is 1 tiny mistake in the blogpost btw: you need execute privs on DBMS_JDWP_DEBUG.

    regards,
    Tony van Esch

    1. JeffS Post
      Author
  7. Pingback: Debugging PL/SQL with SQL Developer: Episode IV

Leave a Reply

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