Using DBMS_DEBUG in SQL Developer

Do you need to debug PL/SQL units in SQL Developer? You can’t get it to work because someone refuses to open TCP ports between your database and your client? No problem. You can still configure the good old DBMS_DEBUG in your SQL Developer. I know it is deprecated since Oracle Database 12c. It is still available in Oracle Database 19c, and when the alternative is to use no debugger at all, then I don’t hesitate too much to use deprecated features.

Special thanks to Jeff Smith for showing me this hidden jewel.

SQL Developer 20.2 and newer

SQL Developer 20.2 has a new setting “use DBMS_DEBUG”

Now, you can switch the debugger without restarting SQL Developer.

SQL Developer 20.1 and older

Step 1 – Find the Configuration Folder ide.system.dir

Start the SQL Developer (should work for 4.0.x and newer). Open the About dialog. Click on the Properties tab. Search for ide.system.dir.

On my MacBook, the folder is named /Users/phs/.sqldeveloper/system18.4.0.376.1900. SQL Developer stores configuration data in this directory. It has a lot of subdirectories. Each deals with a certain subset of functionality.

Step 2 – Close SQL Developer

This is a very important step. We are going to change a configuration file. SQL Developer reads this file on startup and writes it on shutdown. Hence changing the configuration file while SQL Developer is running will have no effect at all.

Step 3 – Change ide.properties

Add the following line to the ./o.sqldeveloper/ide.properties file in the ide.system.dir folder:

DatabaseDebuggerDisableJDWP=true

That’s it. Next time you start SQL Developer DBMS_DEBUG will be used instead of DBMS_DEBUG_JDWP.

Use the Debugger

Start the SQL Developer, connect to a schema, open a PL/SQL unit, compile the code with debug, set a breakpoint and select Debug... from the context menu to start debugging.

In the debugging pane you see that DBMS_DEBUG is used. Therefore debugging works without using a TCP port.

Conclusion

I prefer the DBMS_DEBUG_JDWP package because of its remote debugging capabilities. See Hatem Mahmoud’s blog post for more information about that. However, sometimes it is difficult to get the required access rights in a timely manner. And in such situations, it’s good to know other ways to investigate issues without polluting the code under investigation with additional or temporary logging calls.

Updated on 2020-06-26: SQL Developer 20.2 has a new setting “use DBMS_DEBUG”, see screenshot below. Now, you can switch the debugger without restarting SQL Developer.

Updated on 2021-07-22: I’ve moved the content of the previous update into a dedication section at the top of this blog post. Thank you, Martin Bach for this suggestion via Twitter.