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.
Start the SQL Developer (should work for 4.0.x and newer). Open the
About dialog. Click on the
Properties tab. Search for
On my MacBook the folder is named
/Users/phs/.sqldeveloper/system220.127.116.116.1900. SQL Developer stores configuration data in this directory. It has a lot of subdirectories. Each dealing with a certain subset of functionality.
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.
Add the following line to the
./o.sqldeveloper/ide.properties file in the
That’s it. Next time you start SQL Developer
DBMS_DEBUG will be used instead of
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.
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.