sys@ORA12C> create user demo identified by demo
2 default tablespace users
3 quota unlimited on users;
User created.
sys@ORA12C> grant create session,create procedure to demo;
Grant succeeded.
sys@ORA12C> grant debug connect session to demo;
Grant succeeded.
sys@ORA12C> grant debug any procedure to demo;
Grant succeeded.
sys@ORA12C> grant execute on DBMS_DEBUG_JDWP to demo;
Grant succeeded.
sys@ORA12C> begin
2 dbms_network_acl_admin.append_host_ace
3 (host=>'LT035221',
4 ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
5 principal_name=>'DEMO',
6 principal_type=>sys.XS_ACL.PTYPE_DB) );
7 end;
8 /
PL/SQL procedure successfully completed.
sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> create or replace procedure p as
2 begin
3 dbms_output.put_line('Hello_World');
4 end;
5 /
Procedure created.
demo@ORA12C> alter procedure p compile debug ;
Procedure altered.
demo@ORA12C>When tried to debug from SQL Developer got this error message
Connecting to the database DEMO_ORA12C.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.191.61.86', '60590' )
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 DEMO_ORA12C.
Could you help us to understand what privilege is missing to debug this procedure ?
Your steps look fine, so most likely cause is a name resolution one. For example,
SQL> drop user demo cascade;
User dropped.
SQL> create user demo identified by demo
2 default tablespace users
3 quota unlimited on users;
User created.
SQL> grant create session,create procedure to demo;
Grant succeeded.
SQL> grant debug connect session to demo;
Grant succeeded.
SQL> grant debug any procedure to demo;
Grant succeeded.
SQL> grant execute on DBMS_DEBUG_JDWP to demo;
Grant succeeded.
SQL> begin
2 dbms_network_acl_admin.append_host_ace
3 (host=>'laptop01',
4 ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
5 principal_name=>'DEMO',
6 principal_type=>sys.XS_ACL.PTYPE_DB) );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> conn demo/demo
Connected.
SQL> create or replace procedure p as
2 begin
3 dbms_output.put_line('Hello_World');
4 end;
5 /
Procedure created.
SQL> alter procedure p compile debug ;
Procedure altered.
That all looks fine, but when I try the debugger, I get:
Connecting to the database demo_np12.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '61625' )
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 demo_np12.
In my case, you can see I wanted to connect to 127.0.0.1, so "laptop01" must not be resolving to that. So I added the ACL I needed:
SQL> begin
2 dbms_network_acl_admin.append_host_ace
3 (host=>'127.0.0.1',
4 ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
5 principal_name=>'DEMO',
6 principal_type=>sys.XS_ACL.PTYPE_DB) );
7 end;
8 /
PL/SQL procedure successfully completed.
and then the debugger worked fine.
Hope this helps.