Debugging Oracle Stored Procedures

1. Open PL/SQL Developer

If PL/SQL developer is installed on the machine, open the PL/SQL Developer interface

Enter the user name, password, and host name, which is exactly the same as configured in Web. config in the program, click OK

Locate the package bodies that contains the stored procedure that you want to debug, such as Pack_activity, right-click, select [view] in the pop-up menu, and get all the stored procedures and their code in the package.

2. Adding debug information

To be able to step through the stored procedure, you need to add debug information for the package it is in, right-click the package you want to debug, and select [Add Debug Info] in the popup menu.

This adds debugging information to the package body.

3. Debugging Stored Procedures

Now that all the preparation is done, you can debug the trace stored procedure.

Select the stored procedure you want to debug, right-click, select [Test] in the pop-up menu and go to the test window. The test window has the code to test the stored procedure automatically, and of course you can add and modify the code yourself, which is enough for us to debug the stored procedure. Then follow the steps below to debug.

(1) To add the parameters required for the stored procedure, most of the stored procedures in our project require parameters, and the parameters can be entered in the lower right part of the test window.

such as: getpanno_new need an input parameter V_employeeid, we enter 180, the output parameter is MyCursor, is to view the result, do not need to enter any value.

(2) Start debugging, click [Debug] Menu->[start] (or press F9), go in debug mode, the program began to stop at the begin line.

(3) After the commissioning process and we are familiar with the debugging process is the same:

Run (CTRL+R)

Single Step entry (CTRL + N)

Single Step Skip (Ctrl+o)

Single Step Exit (Ctrl+t)

or click the button on the Debug toolbar: When you press CTRL + N to enter the source code of the stored procedure

In this window, you can view the values and stacks of variables in the procedure.

(4) After the commissioning run, you can view the results as follows (click the button next to the MyCursor variable).

Debugging Oracle Stored Procedures