Configuring environment for debugging PL/SQL Stored procedures

Main objective of this post is to list some of the typical issues I’ve found when debugging PL/SQL stored procedures, functions and packages.

There are many ways of how you can debug server side code, and here is the list of the most common ways I’ve found in practice:

• dbms_debug_jdwp
• dbms_debug
• dbms_output
• database tables used for saving results

Only the first two options we can consider as real debugging, as they allow us to step into the code – line by line.

Last two options are just a time consuming improvisations (when you didn’t configure environment for debugging).

As of 12.2 release, dbms_debug package is deprecated since its replacement dbms_debug_jdwp package is available for years.

Some of the benefits of using JDWP debugger are:

• viewing user defined types (varrays, associate arrays, types, objects, recordsets, nested tables…)
• descriptive exception reporting
• viewing information at different scopes

For all of those reasons, from now on, you should prefer to use a Database management and Development tool which base its debugger on top of the dbms_debug_jdwp package.

Probably the most popular commercial tool (TOAD for Oracle) uses dbms_debug_jdwp from the version 13.1 (released in 2018), while for the 2^nd most popular commercial tool – Allround Automations PL/SQL Developer might still use deprecated dbms_debug package (you need to ask support to provide that information).

Just a few weeks back, JetBrain added support for Oracle database in its popular (also commercial, but community version is also available) DataGrip tool (tool that supports many databases, relational, NoSQL, BigData…), but at the moment, its debugger is based on dbms_debug package.

Support for the JDWP based debugger we can expect sometime later (probably in Q3 2020).

Oracle SQL Developer which is 100% free, is equipped with JDWP debugger for years.

Thus from now on I’ll assume your favorite tool uses JDWP based debugger.

Another assumption is that you are using free Oracle SQL Developer as tool for developing and debugging PL/SQL stored packages, procedures and functions.

How to setup SQL Developer for debugging?

Before starting, first you need to understand what connections will be established when you connect to the database and when you start debug session.

Utility like iptraf-ng on Linux or tcpview or NetView on Windows can be used to list established connections.

On the following figure you can observe connection when you establish connection to the database.

My database server has two network interfaces: 10.0.2.15 (NAT) and 172.25.1.6 (NAT network), while my client IP address is 172.25.1.4.

On the previous figure you can observe that when I connect to the Oracle database, connection between server (IP address: 172.25.1.6, standard database listener port 1521) and my PC (IP address: 172.25.1.4 on dynamically created port 35040) will be established.

The same figure I can get from my client PC:

At the time when I start to use debugger, a new connection will appear.

Pay attention at the connection between 172.25.1.4 (my PC address) over local port 4001 (which I define in Oracle SQL Developer) and 172.25.1.6 (server IP address) on port 15905 (dynamically created port on the server side when dbms_jdwp establish a connection).

Below you can find my Oracle SQL Developer debugger settings:

I usually set only one port for debugging purposes (for this demo it’s port 4001), as it allows me to open only one port on my local firewall for one server address instead to allow all incoming connections.

On the following figure you can observe a classical mistake, when developer allows all remote address and all local ports to accept connections for SQL Developer in the local firewall inbound rules section.

In this case you need to specify “Local Port” 4001 and 172.25.1.6 in “Remote Address” field.

On Linux PC, you can set inbound firewall rules either by using a command line or GUI.

The two most popular local Linux firewalls are firewalld (RedHat, Fedora, CentOS, Oracle Linux) and ufw (Ubuntu and its clones), or IP Tables on older Linux systems.

Since setting up an inbound firewall rules depends on the Linux distribution and the type of firewall, I will leave it to you to configure it on your Linux system.

Below you can find a quick checklist of setting up your environment for debug.

1. Check and configure firewalls settings (start with your client PC firewall, db server firewall and finally network firewall).

2. Compile procedure / function / package for debug:

 

Note a green bug which indicates that stored procedure is compiled with debug option.

 

3. Get (from DBA) privilege:

grant debug connect session to <username>;

 

Example:

grant debug connect session to scott;

 

4. From 12c+ get (from DBA) execute privilege on dbms_debug_jdwp package.

Example:

grant execute on dbms_debug_jdwp to scott;

 

5. From 11g ask DBA to add you in ACL list

For our example it’s enough to execute (as DBA) the following:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
 host => '172.25.1.4',
 lower_port => 4001,
 upper_port => 4001,
 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
 principal_name => 'scott',
 principal_type => xs_acl.ptype_db)
 );
END;

There are many options of ACLs in 11g and later releases and the previous command is just one out of many possible options you can use (your DBA will know for sure what options should be used based on the security policy).

Finally I’ll provide couple of the most common errors you will get when debugging server side procedures / functions and packages.

1. ACL list (from 11g+) prevents successful debugging

When you try to debug your stored procedure, you are getting the following error:

Connecting to the database orcl18c_scott.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '0' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database orcl18c_scott.

 

Solution:

You need to check with DBA to add you in ACL list.

After that you should be able to connect:

Connecting to the database orcl18c_scott.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.25.1.4', '4001' )
Debugger accepted connection from database on port 4001.

2. SQL Developer is throwing the following error when trying to debug stored procedure:

Connecting to the database orcl18c_scott.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '172.25.1.4', '4001' )
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 orcl18c_scott.

In this case a firewall (on your client PC or on database server or in some cases network firewall) is most probably prevents you to establish debug connection.

To confirm this assumption you need to execute the following command (from the database server):

[user@host ~]$ time telnet 172.25.1.4 4001
Trying 172.25.1.4...
telnet: connect to address 172.25.1.4: Connection timed out

real	2m7.311s
user	0m0.003s
sys	0m0.003s

As you can observe, after some delay (around 2 minutes) you’ll get “Connection timed out” message.

Next step is to first check your local firewall, and if you still get the same message, ask sysadmin to check the database server firewall.

The last step is to ask network admin to check.

3. SQL Developer is able to start debugging

If everything is opened (no firewall blocking), and you can start your debug session, you’ll get the following message:

user@host:~>telnet 172.25.1.4 4001
Trying 172.25.1.4...
telnet: connect to address 172.25.1.4: Connection refused

It’s important to understand that port (4001 in this case) will be opened by SQL Developer itself at the time of debugging. It’s not opened all the time like a classical server process.

Additionally, port opened by SQL Developer (e.g. 4001) is not listening and is not prepared to accept new connections.

It’s the dbms_debug_jdwp package that initiate and accept connection from the database server.

Thus start with debugging instead of raising a ticket.

Summary:

Two most common mistakes that I’ve seen so far are:

• Developers are not using Oracle store procedure debugging as they don’t know how to setup your environment correctly
• Developers use database management tools whose debugger is based on deprecated dbms_debug package or even worse, they are still using dbms_output package to get some intermediate results out.
  Additionally I can still find cases in which developers create tables that will be used for debugging purposes (somewhere in the code they’ll put INSERT statement to get the results out).

Hopefully this article will help you to start using a modern debugger that will help you to quickly troubleshoot complex code.

You can find some additional tricks in my previous article:

How to efficiently debug PL/SQL code

at the following link:

https://www.josip-pojatina.com/en/how-to-efficiently-debug-pl-sql-code/

---