Debugging PL/SQL Called By an Application or SQL Scripts

Note:

PL/SQL debugging in Visual Studio Code works with both on-premises and databases in Oracle Cloud. However, Oracle Autonomous Database is not currently supported.

The PL/SQL Debugger can listen for calls to a procedure/function/package and start debugging them when they are called. Any application (web app or command line, using any programming language) or SQL script can make the PL/SQL call, and the application or script can be located on any machine.
When first using PL/SQL debugging, configure both the PL/SQL debugger and the database:
In Oracle Database Explorer, right click on a connection name and select PL/SQL Debugger and Compiler Settings and set the ip address and port number range that will be used by the debugger (the Oracle Database will connect back to your machine using that IP address and one of the ports)
A script to grant privileges and configure the database is provided in the same dialog. Copy the script and then right click on the connection node for the database and select Open New SQL File. Paste the script into the new file, modify it as needed, and then right click in the editor and select Execute All from the menu.
The PL/SQL procedure/function/packages you wish to debug must be compiled with debug information: Right click on the procedure/function/package and select Compile Debug from the menu. The Oracle Explorer icons will change to alert you and also to remind you to issue a Compile when done debugging to restore them to their non-debug state.
Right click on the procedure/function/package and select Open, or Open Package Body from the menu. Set breakpoints as desired (note: conditional breakpoints are not currently supported).
To start the debugger, right click on any connection name and select Start External Application Debugger. The Debug Console will display the IP address and port number that is being used. Make a note of these.
If the PL/SQL call is being made by an application (using Oracle data access driver other than the Oracle JDBC thin client), set a ORA_DEBUG_JDWP environment variable in the environment of the application with the value host=ipaddress;port=portnumber. For example: host=127.0.0.1;port=65000. The IP address and port number should be the same values reported in the Debug Console. This environment variable must be set before the connection is made by the application to the Oracle Database. For example, this can be set in a Visual Studio Code project in the launch.json file in the configurations section (eg "env": { "ORA_DEBUG_JDWP": "host=127.0.0.1;port=65000" } ) or it can be set on a command line before launching the application.
If the PL/SQL call is being made by an application using the Oracle JDBC thin client, or if you cannot enable debugging at the time the connection to the Oracle database is made, modify your code to make a call to the DBMS_DEBUG_JDWP.CONNECT_TCP package procedure at some point in your code before the call is made to the stored procedure/function/or package that you wish to debug. In this call to CONNECT_TCP provide the same IP address and port number reported in the Debug Console. For example, if using JDBC: String sql = "CALL DBMS_DEBUG_JDWP.CONNECT_TCP(?,?)"; CallableStatement callStmt = conn.prepareCall(sql)); callStmt.setString(1, host); callStmt.setString(2, port); callStmt.execute();. At a point in your code after the PL/SQL call is made and debugging is finished, add a call to DBMS_DEBUG_JDWP.DISCONNECT as well.
If the PL/SQL call is being made by an SQL script in Visual Studio Code, add the following line in the SQL script at some point before the call is made to the stored procedure/function/or package that you wish to debug: exec (DBMS_DEBUG_JDWP.CONNECT_TCP('ip address', 'port'));. For example: exec (DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1', '65000')); The IP address and port number should be the same values reported in the Debug Console. At a point in the script after the PL/SQL call is made and debugging is finished, add exec (DBMS_DEBUG_JDWP.DISCONNECT();).
When the application or SQL script is run, a message will appear in the Debug Console stating A database has connected to the PL/SQL Debugger on host: youripaddress and port: yourport. This indicates that the Oracle Database has successfully connected back to Visual Studio Code and debugging can begin.