SQL Developer’s PL/SQL Debugger and Oracle 12c

by Unknown October 06, 2016 0 comments

The PL/SQL Debugger works pretty much out of the box when used with a previous Oracle version. These are the things we needed in place before we could start debugging PL/SQL:

1. A grant of the DEBUG CONNECT SESSION privilege.
2. EXECUTE privilege on DBMS_DEBUG_JDWP.
3. EXECUTE privilege on the stored procedure you want to debug.
4. Make sure the stored procedure is “Compiled for Debug”.

Jeff Smith talks about it in this post.

But what happens when you use Oracle 12c? You still need all the stuff that I mentioned but that’s not enough. See the error I got when I was trying to debug a procedure.

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
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 SCOTT – ORA12CPDB1

Starting with Oracle 12c, if you want to debug PL/SQL stored procedures in the database through a Java Debug Wire Protocol (JDWP)-based debugger, such as SQL Developer or JDeveloper, then you must be granted the jdwp ACL privilege to connect your database session to the debugger at a particular host.

This is one way you can configure network access for JDWP operations:

1 2 3 4 5 6 7 8 9 10 11

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

Note that there are double quotes in name

Host can can be a host name, domain name, IP address, or subnet.

Principal name in the access control entry (ACE) section is the schema that holds the stored procedures you want to debug.

Now the error goes away and you can start your debugging task.

Connecting to the database SCOTT – ORA12CPDB1.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.10’, ‘49428’ )
Debugger accepted connection from database on port 49428.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Inside the Procedure
Process exited.
Disconnecting from the database SCOTT – ORA12CPDB1.
Debugger disconnected from database.

I hope this post saves you some time when you migrate to 12c.

Source: https://galobalda.wordpress.com/2014/02/17/sql-developers-plsql-debugger-and-oracle-12c/