How to enable remote debugging in SQL Developer

Remote debugging is a very cool feature in SQL Developer. I ran into many issues while trying to use this feature.

Install SQL Developer

Download latest version of sql developer and jdk:

I have downloaded:

a) sqldeveloper-4.1.3.20.78-no-jre.zip
b) jdk-8u73-linux-x64.tar.gz


tar -xvzf  jdk-8u73-linux-x64.tar.gz 


unzip sqldeveloper-4.1.3.20.78-no-jre.zip
cd sqldeveloper
chmod +x sqldeveloper.sh
./sqldeveloper.sh

When you see below message, give the location where you have extracted jdk:

"Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored under your home folder.in /home/rajiv/.sqldeveloper/4.1.0/product.conf"

For eg in my box I gave below location (where i have extracted jdk)
/scratch/rajiv/sqldeveloper/jdk1.8.0_73

Enable remote debugging

Connect as sys and grant below privileges.

grant DEBUG CONNECT SESSION to SCHEMA_NAME;
grant DEBUG ANY PROCEDURE to SCHEMA_NAME;

SCHEMA_NAME is the db user name to which you want to connect from SQL Developer for debugging (ex: SCOTT)

Run below anonymous block to add the client host to ACL

--add ip address of the host where sql developer is running
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
host => 'IP_ADDRESS',
lower_port => null,
upper_port => null,
ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
principal_name => 'SCHEMA_NAME',
principal_type => xs_acl.ptype_db)
);
END;

Otherwise below error is thrown while trying to connect to debugger port.

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1

You need to compile the PL/SQL package or procedure using "Compile for Debug" option first.
Now you can set breakpoints in the PL/SQL code. And then right click the procedure name and select "Debug" option. This will show a popup, where you need to provide some value for the input parameters and hit "OK".

Rajiv Kuriakose