How To Run Stored Procedures in Debug Mode? - Oracle DBA FAQ - Introduction to Oracle SQL Developer

If have an existing stored procedure and you want to debug it interactively, you can use the debug feature provided in SQL Developer. The following exercise shows you how to start the debug mode:

• Open you connection name, like Local_XE.
• Open Procedures.
• Right-click the procedure name: HELLO.
• Select Debug. The Debug PL/SQL window shows up. SQL Developer creates a simple anonymous code block for you to run the stored procedure.
• Click OK.

You may get the following errors. Read the next tutorial on how to fix the errors.

Connecting to the database Local_XE.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP(...)
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
This session requires DEBUG CONNECT SESSION and DEBUG ANY
  PROCEDURE user privileges.
Process exited.
Disconnecting from the database Local_XE.