Updated September 2020
This is going to be a FAQ. I get these questions all the time, and I need a place to send people when I don’t have time to live-demo.
If your question isn’t below, add yours as a comment.
If you want to know what the debugger is or why they might try it over DBMS_OUTPUT, read this.
Or skip all this and just go through the slides and YouTube video
Need more, here you go:
How do I start a debug session?
There are two debugger interfaces, DBMS_DEBUG and the newer, and more powerful Java Debug Wire Protocol (JDWP) version.
SQL Developer has from the beginning, ONLY supported JDWP. However this requires that your database is able to CONNECT back down to YOUR CLIENT to start a debug session.
Starting with version 20.2, we now also support the DBMS_DEBUG interface which simply opens a 2nd client based connection to the database for debugging – much simpler, but slightly less feature rich.
Either way, starting a debug session is the same set of steps…
Open your PL/SQL object in our code editor – NOT in a SQL Worksheet. The code editor is the actual IDE that has the cool stuff you need whereas the SQL Worksheet is the ‘dumb’ editor you can run any SQL or PL/SQL through.
Compile for debug. You CAN skip this step, but if you do, certain things won’t work – like breakpoints.
Hit the debug button. Supply any required input parameters and hit OK.
You’re now debugging!
Wait, that didn’t work!
Either you don’t have the required privs to run a debug session OR the firewall is getting in the way OR you’re in Database 12c and you haven’t setup the Access Control List (ACL.)
- Execute on the procedure – remember, debugging a proc means you’re EXECUTING the proc
- DEBUG CONNECT SESSION
- and maybe DEBUG ANY PROCEDURE
When we start a debug session, the debugger is actually running on your client machine. The database reaches out to your machine. That’s right, it’s Server -> Client NOT Client -> Server.
You can see this in the debug panel.
So, if you’re a developer and want to use our debugger, you need to work with your DBA or network admins to open up the firewall such that your database server can make it to your client.
You can do a couple of things to make this easier. You can have SQL Developer prompt you for the network name/address to use and you can define the port range you want to use.
In Database 12c there is a new security protocol in place that prevents the database from just reaching out to things on the network [DOCS]. You have to set it up such that it can make a JDWP call, to a place or range of places on the network, and for your database user account.
My co-worker Colm Divily wrote a great blog on this – and it goes into great depth on how to get your VirtualBox networking options correct if you’re running SQL Developer on your host and debugging a database running in a virtual machine.
Update for 20.2 DBMS_DEBUG
The stuff up above about ACL, that’s the tricksy part of the JDWP interface – you’re likely going to require DBA (if not also a network engineer) to intercede on your behalf to set it up.
Skip that, go straight to DBMS_DEBUG
I tried to debug, but nothing happened.
By default the debugger is set to run until it hits a breakpoint. If you don’t have a breakpoint set, it will just run your program.
I have my copy of SQLDev set to debug and not do anything until I tell it to. That way if I forget to set a breakpoint, no problem.
Ok, I’m debugging, now what?
Step through your code, see what’s happening.
You can also say ‘Step Into.’
Which will do exactly what ‘Step Over’ does unless:
But I want to SEE STUFF too!
If the watches aren’t working, you probably didn’t compile for debug.
I wrote this post that goes over the data, smart data, and watches panels in more detail.
I want to CHANGE stuff!
This is where it gets cool, really cool. Instead of changing your code to make a scenario happen, you can edit the value of a variable in the debug session to force it.
All of this stepping takes too much time!
Agreed. Use breakpoints.
Click in the gutter to add a breakpoint. Hit execute or resume. The debugger will run until it hits your breakpoint – assuming that either the breakpoint is on an executable line AND that your breakpoint doesn’t have a pass count or condition attached to it.
I should have said this post would be mostly everything.
I’ve already made the post too long, but if you want to know more, ask away!
I’ll end the post with one last screenshot – it shows how I have my desktop setup in SQLDev when I’m debugging. You have a ton of flexibility as to what you want open, what to be displayed, and where.