Activating and using debug in SQLDeveloper

Before to use debug in SQLDeveloper we need to prepare the database to accept this request and we need to add grants to the user that activate a debug.

Preparing database environments:

create role SQLDEV_DEBUG_ROLE;

begin
 dbms_network_acl_admin.append_host_ace(host=>'*', 
                                        ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') , 
                                        principal_name=>'SQLDEV_DEBUG_ROLE', 
                                        principal_type=>sys.XS_ACL.PTYPE_DB) );
end;
/

grant debug connect session to SQLDEV_DEBUG_ROLE; 
grant debug any procedure to SQLDEV_DEBUG_ROLE;
grant execute on DBMS_DEBUG_JDWP to SQLDEV_DEBUG_ROLE;

grant SQLDEV_DEBUG_ROLE to scott;

--to Check
select * from dba_network_acls;
select * from dba_network_acl_privileges;
select host from dba_host_acls;

--Check firewall port from 4000 and 4199 (in both direction client/server)

Output example from SQLDeveloper:

sqlplus / as sysdba

grant SQLDEV_DEBUG_ROLE to scott;

CREATE OR REPLACE EDITIONABLE PROCEDURE "SCOTT"."DEMO_PROCEDURE" AS 
 vtime timestamp;
BEGIN
 select current_timestamp into vtime from dual;
 dbms_output.put_line('Result: '||vtime);
END;
/

From SQLDeveloper

Compiling the procedure for debug:
DebugSQLDevCompile

Setting breakpoints with mouse click on the right rows:
DebugSQLDevBreak

Running procedure in debug mode:
DebugSQLDevRunDebug

Using debug functionality:
DebugSQLDevUseDebug

Optional: You can change the debug ports from preferences.

DebugSQLDevPort

Leave a Comment