PL/SQL Debugging Setup

In order to debug a PL/SQL program in an Oracle Database, you must do the following:

  • Grant database privileges that allow you to debug programs.

  • Compile the program with debug information. You can use the Oracle Developer Tools Compile Debug command to compile the program with debug information.

  • In the PL/SQL Debugging Options page, set a valid IP address, port range, and indicate which Server Explorer connection contains the PL/SQL code that will be debugged.

This section includes the following topics:

Granting Database Privileges for Debugging

Performing PL/SQL debugging requires certain database privileges. These can be easily granted through the Grant Debugging Privileges Dialog. The specific privileges are as follows:

  • The DEBUG CONNECT SESSION privilege is required. This can be granted with GRANT DEBUG CONNECT SESSION TO username.

  • The DEBUG ANY PROCEDURE privilege or both DEBUG and EXECUTE privileges on the PL/SQL program being debugged is required. These can be granted with GRANT DEBUG ANY PROCEDURE TO username

  • In Oracle Database 12.1 or later, additional privileges are required which grant the database explicit access to an IP address and port range on the computer where Visual Studio is installed. These privileges can be granted through the Grant Debugging Privileges Dialog or by manual execution of the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE package procedure as SYSDBA.

    For example, execute the following command as SYSDBA, replacing the host, lower port, upper port, and principal name: 

    BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    HOST => '255.255.255.25',
    LOWER_PORT => 61000,
    UPPER_PORT => 65000,
    ACE => XS$ACE_TYPE(PRIVILEGE_LIST => XS$NAME_LIST('jdwp'),
                       PRINCIPAL_NAME => 'HR',
                       PRINCIPAL_TYPE => XS_ACL.PTYPE_DB));
END;

    To revoke this privilege, execute: 

    BEGIN
   DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE(
     HOST => '255.255.255.25',
     LOWER_PORT => 61000,
     UPPER_PORT => 65000,
     ACE => XS$ACE_TYPE(PRIVILEGE_LIST => XS$NAME_LIST('jdwp'),
                        PRINCIPAL_NAME => 'HR',
                        PRINCIPAL_TYPE => XS_ACL.PTYPE_DB));
 END;

    For more information on the DBMS_NETWORK_ACL_ADMIN package, see Oracle Database PL/SQL Packages and Types Reference.

Set PL/SQL Debugging Options

A listener is started in Visual Studio for debugging session connections from the database. To ensure that the database can connect to this listener, a valid IP address and port number must be provided. Additionally, Oracle Developer Tools needs to know which Server Explorer connection contains the PL/SQL code to be debugged. All of these values should be set in the PL/SQL Compiler Settings Options Page.