Debugging Procedure using SQL developer

Some days I must using SQL Developer for debugging our procedures. But I face the error ORA-24247: network access denied by access control list (ACL). This error is requested by many people on the internet. That is the reason I decide to write this.

My environment for this article:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Oracle SQL Developer  4.1.0.19

Username: ora41

Database server IP: 192.168.10.200

Client IP(where you are running SQL Developer): 192.168.10.1

Before ora41 user can debug procedures. You must login as dba role and grant privileges:

grant DEBUG CONNECT SESSION to ora41; grant DEBUG ANY PROCEDURE to ora41; BEGIN   DBMS_NETWORK_ACL_ADMIN.append_host_ace (     host       => '192.168.10.1',     lower_port => 40000,     upper_port => 50000,     ace        => xs$ace_type(privilege_list => xs$name_list('connect', 'jdwp'),                               principal_name => 'ora41',                               principal_type => xs_acl.ptype_db)); END; / --review the acl select * from DBA_HOST_ACLS; select * from DBA_HOST_ACES;

After that you must configure SQL Developer on the client to run debug service in a port range as you has granted above. Go to menu Tools --> References…

And then you can open Oracle SQL Developer, connect as ora41 and debug your  procedures.

If your configuration is not correct. You can have the following errors:

Connecting to the database 192.168.10.200 ora41. Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.10.1', '1000' ) 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 192.168.10.200 ora41.