Debugging Setup Checklist

There are several steps required to configure PL/SQL debugging for first use. If one of the steps is skipped, several possible errors can occur.

To avoid any missing steps and get started debugging your PL/SQL code more quickly, perform the following steps:

Make sure the database you use is version 10.2 or later.
If you have SYSDBA privileges on the database:

In a SYSDBA connection in Server Explorer, from the context menu for the User node (in Objects View) or a Schema node (in Schemas view), representing the user who will be doing the PL/SQL debugging, select Grant Debugging Privileges menu item.

In the Grant Debugging Privileges Dialog that appearsprovide the IP Address for the computer where Visual Studio is installed and the starting port and ending port numbers representing the range of ports Oracle Database could use to connect back to that computer.
If you do not have SYSDBA privileges on the database, ask your DBA to execute the following commands as SYSDBA for the user who will be doing the debugging:
```
grant debug any procedure to username
```
and
```
grant debug connect session to username
```
For the command, as an alternative, both debug and execute permissions may be granted to the user, on the PL/SQL program being debugged.

Additionally, ask your DBA to execute the following commands 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;
```
Compile the PL/SQL program units with debug information, as follows:

In Server Explorer, find the node that represents the package, procedure, or function that you want to debug. Right-click it, and from the menu select Compile Debug. The icon next to the PL/SQL procedure or function changes to indicate that it has been compiled with debug information.
Set the PL/SQL Debugging Options from the Visual Studio Tools menu by selecting Options, then Oracle Developer Tools in the Options list, and performing the following:
1. From the IP Address list, choose the IP address for this computer.
  
  During PL/SQL debugging, the Oracle Database connects to Visual Studio using this IP address. IPv6 addresses are only supported with Oracle Database 11g Release 2 (11.2) or later.
  
  Note: For Oracle Database 12.1 or later, this must be an IP address that was granted privileges in steps 2 and 3 above.
2. Make sure that the port range specified represents open ports on the computer that Visual Studio is installed on and that the ports are not blocked by a firewall.
  
  During PL/SQL debugging, the Oracle Database connects to Visual Studio through TCP/IP on a random port within the specified range.
  
  Note: For Oracle Database 12.1 or later, this port range must be equal to or a subset of the port range that was granted privileges in steps 2 and 3 above.
3. Check the box next to your connection in the Available database connections list.
  
  This tells the PL/SQL debugger to look for the PL/SQL code using this connection.
If your procedure or function contains input parameters that can be entered by hand (that is, scalar values), begin debugging by right-clicking on the procedure or function name in Server Explorer and choosing Step into or Run Debug (if a break point is set). This is known as "Direct Database Debugging Mode".
If you intend to debug a non-website client-server (Console, WinForm, and so on) application using a single instance of Visual Studio to step from .NET code into PL/SQL code and back ("Multitier Application Debugging Mode"), you must perform steps 8 through 13 in this checklist. If the PL/SQL is being called by a middle tier and/or you wish to use two instances of Visual Studio, one for .NET and one for PL/SQL ("External Application Debugging Mode"), skip to step 14 of this checklist.
From the Visual Studio main menu, select Project, then select Properties (the .NET application project must be loaded).

Click the Debug tab and uncheck Enable the Visual Studio hosting process in the Enable Debuggers list. If you do not do this, you must run the debugger once and then stop it in order to make it work correctly.
Turn on the Application level debugger by selecting Tools, then Oracle Application Debugging from the Visual Studio menu, and ensure there is a check next to Oracle Application Debugging.
Verify that the ODP.NET config file (machine.config, web.config, or app.config) of the application does not have the ORA_DEBUG_JDWP value set. If it is set, disable it.
Set a breakpoint in the PL/SQL program. You can also set breakpoints in your .NET application code at some line of code after the call to the PL/SQL program, if you want. That stops execution after you return from debugging the PL/SQL.
Build and begin debugging your .NET application, and the PL/SQL breakpoint will fire. You can then look at the live data being passed back and forth between your .NET application and the PL/SQL procedure, through the watch window. In case of problems, examine the Oracle Output Window.
When done debugging, select Tools, then uncheck Oracle Application Debugging from the Visual Studio menu.
If you wish to debug a Web application or otherwise use a dedicated Visual Studio instance only for PL/SQL debugging (and another Visual Studio instance for .NET debugging), perform the following steps:
1. In the menu for the Visual Studio instance that will debug the PL/SQL, select Tools, then check External Application Debugging.
2. Provide IP address and port number.
  
  Note: For Oracle Database 12.1 or later, the IP address and port number must have been given permissions in steps 2 and 3 above.
3. For a web application or any other application that is calling a PL/SQL Stored procedure or function, make sure that ORA_DEBUG_JDWP is set either as an environment variable or in the ODP.NET config file (machine.config, web.config, or app.config) of the application. This value must be set before the application or website connects to Oracle. A config file value will take precedence over any environment variable.
  
  Note: Only ODP.NET version 12.1.0.2 or later supports the ORA_DEBUG_JDWP config file entry. For earlier versions, use an environment variable instead.
  
  Config file example:
```
<oracle.manageddataaccess.client>
  <version number="*">
    <settings>
      <setting name="ORA_DEBUG_JDWP" value="host=127.0.0.1;port=1234"/>
    </settings>
  </version>
</oracle.manageddataaccess.client> 
```
  Environment variable example:
```
set ORA_DEBUG_JDWP=host=127.0.0.1;port=1234 
```
4. Set a breakpoint in your PL/SQL program.
5. Run the web application or external application and connect to Oracle. In case of problems, examine the Oracle Output Window.
6. After the breakpoint fires and you are done debugging, stop debugging from the Visual Studio Menu.