How to Start the PL/SQL Debugger

thatjeffsmith SQL Developer 41 Comments

Tell Others About This Story:

Starting the debugger CAN be as simple as hitting this button in Oracle SQL Developer:

The second button on the PL/SQL Editor toolbar

The second button on the PL/SQL Editor toolbar

But

But, you need to make sure of the following:

  • You have compiled for debug
  • You have the privs
  • You have a breakpoint set OR you have set the debugger preferences to start with a ‘Step Over’
  • On Database 12c, you’ve done setup the network access control list

That’s a lot of buts. Let’s plow through them!

Wait, wait, wait. Before you do anything else: start SQL Developer, connect to your database, and load your PL/SQL object into the Procedure Editor (not the worksheet!)

Ok, now we can start.

Compile for Debug

Don’t do this in production – it adds overhead for working with and executing the PL/SQL. But you really need to do it in the development instance if you want to debug. It’s the default ‘compile’ operation in SQL Developer. We assume you want to DEBUG with the IDE. But if you want to be explicit, do this:

The third button on the procedure editor toolbar.

The third button on the procedure editor toolbar.

Doing this will allow for things like watches and breakpoints to work. Without those, debugging is kind of boring.

You have the privs.

You need EXECUTE on the object and DEBUG CONNECT SESSION.

Set a breakpoint.

Find an executable line of code. Not a comment. Not a declaration (unless it’s also an assignment.) Now click in the gutter space.

You can also hit F5 to toggle breakpoints.

You can also hit F5 to toggle breakpoints.

Wait, I don’t want a breakpoint!

Ok, open the preferences and set your debugger to do this:

If you don't change this, debugging without breakpoints won't give you an opportunity to do anything.

If you don’t change this, debugging without breakpoints won’t give you an opportunity to do anything.

With it set to ‘Step Over,’ the debugger will stop on Line 1. From there, the debug session is up to you. If you have it set to ‘Run Until Breakpoint Occurs,’ then the procedure will run until it finds a reason NOT to run. Without exceptions or breakpoints, you’ll just see the debugger log say, ‘Finished’ – and that’s no fun.

So set a breakpoint, or don’t set a breakpoint – but make sure the debugger is set to do what you want it to.

And Now You’re Cooking With Gas

Ok, NOW hit the debug button.

You’ll get prompted for the inputs in the anonymous block popup editor. Hit OK, and you’re debugging.

You can step, line by line, or you can say 'Resume' - which will go until you hit a breakpoint.

You can step, line by line, or you can say ‘Resume’ – which will go until you hit a breakpoint.

One More Thing…Database 12c

There’s an extra security level in 12c. To get access to JDWP, you need to make it available to your users. Friend of the community Galo Balda wrote this just the other day.

This may be my last post on the debugger, but I doubt it.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 41

  1. Solved as follows
    I created a tunnel with reverse option on the host:
    ssh -pPORT_FOR_ACCESS -R 4000:127.0.0.1:4000 root@(VM_IP)

    Port 4000 and IP 127.0.0.1 should be fixed in SQLDEVELOPER parameters;

  2. “It’s the default ‘compile’ operation in SQL Developer.”
    It would be nice if that was an option in preferences or even in the connection settings (per database). ie, PROD default DEBUG=no; TEST default DEBUG=yes. I’m not saying to disable it all together, just have a default setting that each user can control.

  3. I’m running XE on my local machine and would like to debug a function in a package. Somehow the debugger process is not starting after I’ve compiled the package and body for debug; however, the debug listener starts, and I can then manually execute the following statements in an SQL worksheet for the connection:
    ALTER SESSION SET PLSQL_DEBUG=TRUE;
    CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘127.0.0.1’, ‘4000’ );
    which produces this success message:
    Debugger accepted connection from database on port 4000.
    But thereafter I can’t step through the code – the Step Into, Step Over etc. buttons are greyed out, and irrespective of my debugger options settings (Run until a breakpoint occurs, Step over) no statement pointer appears. I wonder what is missing?

    1. thatjeffsmith Post
      Author
      1. Hi Jeff, thank you for your reply. I don’t see anything. The panel appears, but no commands are issued in it – it’s empty. At the lower right the status area tells me I am ‘Launching – Starting target’, but apparently nothing gets launched; the .jpr process apparently doesn’t start. If I press the GUI debug button again, I am prompted to enter another port (since the initial port, 4000, is already being listened on), and after doing that I get another row in the lower-right stack of ‘Launching – Starting target’ bar indicators. I can keep doing this and get several bars stacked up at the lower right, but the window for the debugging process that shows me the plsql source, breakpoints and everything doesn’t appear. (But the IDE does not freeze up.) In the Processes tree at the left, no debugging process is shown – just the “Debug Listener” process.

      2. Hi again Jeff, I haven’t been able to get the debugger to run the normal way, but the following steps allow me to debug. I wonder if this helps to diagnose the cause of the problem? Also, do you know whether Java outputs any log messages that I could look at while trying to invoke the debugger the normal way that might give a hint about the cause?

        1. Set the ‘Start Debugging Option’ to ‘Run Until a breakpoint occurs’.

        2. Open an SQL worksheet on the desired connection.

        3. Execute the following within that SQL worksheet:

        ALTER SESSION SET PLSQL_DEBUG=TRUE;

        4. Open the package body containing the routine(s) to be debugged and, after compiling for debugging, press the GUI “Debug…” button. Prepare the anonymous block with the desired parameters, copy the block onto the clipboard, and press OK; the Processes tree panel on the left then shows that a process is listening on port 4000.

        5. In the package body that was opened, set a breakpoint in the routine to be debugged.

        6. Execute the following statement within the SQL worksheet that was opened:

        CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘127.0.0.1’, ‘4000’ );

        7. Paste the anonymous block that was copied above in step 4 into the SQL worksheet and run it. (If a message pops up beforehand about using the package version on the database instead of the displayed one, press ‘OK’.) The debugger should run and stop at the breakpoint.

        I would be grateful for any suggestions you may have. Also I wonder what you were doing at the Montgomery Regional Airport a few years ago? I grew up close by and am always surprised if anyone arrives there other than by accident 🙂

      3. Hi Jeff, I discovered a solution to my debugging problems. This tip may help someone else, so here it is: I removed the “SQL Developer” folder from C:\Users\\AppData\Roaming\ (on Windows 8.1). When I then restarted SQL Developer, it rebuilt the folder; I had to define my connections again and set my preferences, but the debugger now works completely normally. I’m well chuffed!

    1. thatjeffsmith Post
      Author
  4. I face a very strange problem. When I am trying to start local debugging by pressing the bug-button, then at processes panel, it appears the debug listener (with port 8080) and I cannot proceed with debugging. Debug listener does not swift to the local debug icon (the red bug). Any idea where to look?

    thanks

  5. Hi Jeff,

    how do I have to configure SQL Developer to debug a remote database via a tunnel? How do I have to configure the tunnel? Unfortunately I receive

    PL/SQL wird ausgeführt: ALTER SESSION SET PLSQL_DEBUG=TRUE
    PL/SQL wird ausgeführt: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘127.0.0.1’, ‘63062’ )
    ORA-30683: Fehler beim Herstellen von Verbindung zu Debugger
    ORA-12541: TNS: Kein Listener
    ORA-06512: in “SYS.DBMS_DEBUG_JDWP”, Zeile 68
    ORA-06512: in Zeile 1
    Prozess beendet.

    In normal run mode everything works fine…

    Best regards
    Matthias

  6. Hello, I resolved this problem, only give a permissions on my Firewall. We need to give permission from Data Base (server) to see a segment network on my company. My case of problem, when a user want to debug, the data base server not see my client my machine (My IP) so, we need to give a permission on my Firewall and that’s it.

    1. Mr. Rodriguez,

      I understand that there may be a language barrior in you communications but, could you please try to describe what you did to resolve the debugger connection problem?

      What permissions were given on your firewall?

      Thanks for your time and patience.

      IsleOracle

  7. Weird problem on SQLDEVLEOPER v 4.0.2.15. When I fire off the debugger most of the time on this largish package I am working on, it pops another tab with the same package body, and breaks on the last line of the package body. I have not seen this behavior in v3.x

    Any thoughts?

    1. thatjeffsmith Post
      Author
  8. Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ’192.168.250.36′, ’55640′ )
    ORA-30683: failure establishing connection to debugger
    ORA-12541: TNS:no listener
    ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
    ORA-06512: at line 1
    Process exited.

    Disabled the windows firewall & tried but same error persists now the connecting port has changed can we force sql dev to connect of the fixed port like 4000 ?.

    Thanks

    1. Based on my experience with SQL Developer, you can set the port by going to Tools > Preferences > Debugger >

      Debugging Port Range – set the minimum and maximum to 4000.

      That should set the fixed value.

  9. SQL Dev requires port 4000 to be open to start the debug session.I can ask the security admin to open the 4000 port but can’t ask the security admin to open the ports random ports like 55640 considering the security concern.

    Can we force sql dev to use the specified port only like 4000 on sql dev version 4 ? If not then what would be the other work around.

    Thanks

  10. Hi Jeff,

    Developers are using sql developer & the below errors were reported while debuggging the procedure.
    ORA-30683 : failure establishing connection to debugger
    ORA-12535 : TNS : operation timed out
    ORA-06512 : at “SYS.DBMS_DEBUG_JDWD”,

    Developers can connect normally to the database but the problem is occuring when they start debugging.

    Please update needs to be done to solve the issue.

    Regards,
    Shadab

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. Can the database connect to your machine? i:e you mean to say that whether i am able to ping to my desktop isn’t it ?

            First the errors were different now the errrors are different.I am getting below errors now.

            Could you please assist ?.

            Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
            Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.250.36’, ‘55640’ )
            ORA-30683: failure establishing connection to debugger
            ORA-12541: TNS:no listener
            ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
            ORA-06512: at line 1
            Process exited.

            Thanks

          2. thatjeffsmith Post
            Author

            The database server is trying to connect to your machine at that IP on port #55640 – so make sure Windowws Firewall or network security isn’t preventing that.

            Ping, basically. You could try to do a telnet FROM the database server to your SQLDev client machine on port 55640 to test.

  11. I could successfully follow the steps till setting a breakpoint.
    But I find that some debugging menu options are greyed out(disabled).
    All the menu options in the Run Menu are disabled/greyed out.
    It’s bugging me a lot. I have been searching this almost for a month now over the internet. Please guide me.
    It’s my standalone machine : Oracle 11g, sqldevelopr 3.2, XP, VirtualBox, Host-Mac OS X Mav.

    Thanks

  12. MSimms – I see what you are talking about there – getting hung up at connection. I have your exact problem. Did adding the Domain Connections exception for sqldeveloper.exe work? I am anxious to get the “super powers” of a dynamic debugger. 🙂

  13. Under Domain Networks, it is set to block all connections to programs not on the exception list.
    I’ll check to make sure the SQLDEVELOPER.EXE is on the list.

  14. I cannot get the debugger to initiate:
    Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
    Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘10.117.46.69’, ‘62142’ )
    ORA-30683: failure establishing connection to debugger
    ORA-12535: TNS:operation timed out
    ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
    ORA-06512: at line 1
    Process exited.
    Disconnecting from the database EM-Test.

    I did get the DBA to provide authorization, but the connection appears to be hosed. Using 4.0.2 release.
    Note: I can establish a breakpoint, but the watches are disabled.

    1. thatjeffsmith Post
      Author

      Hey Mark,

      The database makes a connection to your client to start a debug session – is there a firewall preventing the database server from reaching your desktop?

  15. I am looking for a way to interrupt our nightly build process in case of compilation errors. Currently we are using dbms_utility.compile_schema.

    I did not found such option there. Do SQL developer provide an option to to compile schema from commadn line and abort schema compilation in case of any compilation error (or did you know how to do this in sqlplus)?

    many thanks in advance

    ole

  16. Any hints, links or suggestions about running the debugger in v4 over a VPN? I appreciate such a scenario is rather complex with firewalls and routers in the way – interested to hear if anyone is doing this successfully.

    1. thatjeffsmith Post
      Author

      It all comes down to can your database server initiate a connection back to where SQL Developer is running. So first things first, can the server ping your machine, and is there a outbound firewall for TCP on the default debugger ports which start at 4000 or so.

      1. Thanks Jeff, it was an errant network ACL which was preventing the server connecting back to the local VPN client – now all under control and working fine.

        1. Could you please explain in detail what changes you made to get the DEBUGGER working over VPN. I suspect I might also be facing the same issue

          Thanks

    1. thatjeffsmith Post
      Author

      JDWP doesn’t currently support running queries over the debug session. It’s something we’re looking into though, so stay tuned 🙂

      For now you’d have to actually query that into a pl/sql table and use a watch…a lot of work, but that’s how it is.

Leave a Reply

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