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

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.

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:

 host => '',
 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)
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.
Debugger accepted connection from database on port 49428.
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.

3 Comments on “SQL Developer’s PL/SQL Debugger and Oracle 12c”

  1. […] get access to JDWP, you need to make it available to your users. Friend of the community Galo Balda wrote this just the other […]

  2. raperswil says:

    What IP is required here :

    host => ‘’,

    Is it the IP of the DB server [where I believe the debugging is run] or the IP of the client where the SQL developer program runs ?

    • gbalda says:

      It’s the ip of the computer where SQL Developer runs. The ACL allows the database server to initiate a connection back to where SQL Developer is running.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.