) How to debug stored procedures in oracle

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. This is exactly the same as the configuration in web. config in the program. Click OK.

Find the Package bodies of the stored procedure to be debugged, such as PACK_ACTIVITY. Right-click the Package and choose [view] From the shortcut menu to get all the stored procedures and their code in the Package.

 

2. Add debug information

To track the stored procedure in one step, you need to add the debug information for the package. Right-click the package to be debugged and select [add debugging information] in the pop-up menu.

In this way, the debugging information is added to the package body.

 

3. debug the Stored Procedure

Now all the preparations are ready. You can debug and track the stored procedure.

Select the stored procedure to be debugged, right-click it, and select [test] from the menu to go to the test window. the test window contains the code automatically generated to test the stored procedure. Of course, you can also add and modify the code yourself. Currently, we only want to debug the stored procedure, the automatically generated code is enough. Perform the following debugging.

(1) parameters required for adding stored procedures. Most stored procedures in our project require parameters. You can enter parameters in the lower-right part of the test window.

For example, GetPanNO_New requires an input parameter v_employeeid. We enter 180, and the output parameter is mycursor, which is used to view the result without entering any value.

(2) start debugging. Click the [debugging] menu-> [start] (or press F9) To Go To The debugging mode. The program stops at begin.

(3) The subsequent debugging process will be the same as the debugging process we are familiar:

Run (Ctrl + R)

Single Step (Ctrl + N)

Skip one step (Ctrl + O)

Exit in one step (Ctrl + T)

Or click the button on the debug toolbar: after pressing Ctrl + N to go to the source code of the stored procedure

In this window, you can view the variable values and stacks in the process.

(4) After debugging, you can view the following results (click the button next to the mycursor variable ).

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. This is exactly the same as the configuration in web. config in the program. Click OK.

Find the Package bodies of the stored procedure to be debugged, such as PACK_ACTIVITY. Right-click the Package and choose [view] From the shortcut menu to get all the stored procedures and their code in the Package.

 

2. Add debug information

To track the stored procedure in one step, you need to add the debug information for the package. Right-click the package to be debugged and select [add debugging information] in the pop-up menu.

In this way, the debugging information is added to the package body.

 

3. debug the Stored Procedure

Now all the preparations are ready. You can debug and track the stored procedure.

Select the stored procedure to be debugged, right-click it, and select [test] from the menu to go to the test window. the test window contains the code automatically generated to test the stored procedure. Of course, you can also add and modify the code yourself. Currently, we only want to debug the stored procedure, the automatically generated code is enough. Perform the following debugging.

(1) parameters required for adding stored procedures. Most stored procedures in our project require parameters. You can enter parameters in the lower-right part of the test window.

For example, GetPanNO_New requires an input parameter v_employeeid. We enter 180, and the output parameter is mycursor, which is used to view the result without entering any value.

(2) start debugging. Click the [debugging] menu-> [start] (or press F9) To Go To The debugging mode. The program stops at begin.

(3) The subsequent debugging process will be the same as the debugging process we are familiar:

Run (Ctrl + R)

Single Step (Ctrl + N)

Skip one step (Ctrl + O)

Exit in one step (Ctrl + T)

Or click the button on the debug toolbar: after pressing Ctrl + N to go to the source code of the stored procedure

In this window, you can view the variable values and stacks in the process.

(4) After debugging, you can view the following results (click the button next to the mycursor variable ).