How to debug stored procedures in Oracle

Oracle database is a relational database management system widely used by major enterprises. Its powerful functions and stability are favored by people. In the actual development process, developers often need to write stored procedures to implement various business logic, and may encounter some problems when debugging stored procedures.

This article will introduce how to debug stored procedures in Oracle database.

1. Use SQL Developer to debug stored procedures

SQL Developer is a free database development tool officially provided by Oracle. It provides a powerful debugger that can be used to debug PL/SQL storage. Procedures, functions, triggers, etc. Below we will take a simple stored procedure as an example to demonstrate how to use SQL Developer to debug stored procedures.

1. Create a stored procedure

First, we need to create a simple stored procedure:

CREATE OR REPLACE PROCEDURE my_proc (p_input IN NUMBER, p_output OUT NUMBER) AS
BEGIN
p_output := p_input * 2;
END;

This stored procedure receives an input parameter p_input, and multiplies it by 2 and assigns it to the output parameter p_output. In real scenarios, stored procedures will be more complex, but the debugging methods demonstrated in this article are also applicable to complex stored procedures.

2. Set breakpoints

Next, we need to set breakpoints in the stored procedure to pause the program flow and check variable values ​​during execution. Setting a breakpoint in SQL Developer is very simple. You only need to select the line where you want to set a breakpoint in the code of the stored procedure, and then click on the blank area next to the line number. As shown in the image below, I set a breakpoint on line 3.

3. Execute the stored procedure

Now, we can execute the stored procedure in SQL Developer and pause the program flow during the running process to check the variable value. We can use the following code to execute the stored procedure and pass a parameter value:

DECLARE
v_input NUMBER := 10;
v_output NUMBER;
BEGIN
my_proc(v_input, v_output );
END;

After executing this code, SQL Developer will automatically open the debugger and pause the program on line 3, as shown in the figure below. We can see that the value of v_input is 10, and during the step-by-step execution, the value of p_output is also assigned to 20.

4. Check variable values

While the program is paused, we can check the value of each variable. You can view the value of a variable by clicking its name in the code or by using the Monitor window. In SQL Developer, you can view the values ​​of multiple variables at the same time by selecting the menu Debug > Debugging Windows > Watches and adding variables in the monitor window.

As shown in the figure below, I added v_input, v_output and p_output to the monitor window, and you can see that their values ​​are 10, 0 and 0 respectively.

Next, we can let the program continue executing until the next breakpoint or the end of the program by clicking the "Continue Execution" button at the top of the debugger window.

2. Use the DBMS_DEBUG package to debug stored procedures

If you do not have the SQL Developer tool, you can also use the DBMS_DEBUG package provided by Oracle to debug stored procedures. Below we will introduce how to use the DBMS_DEBUG package.

The DBMS_DEBUG package is a tool package provided by Oracle Database, through which you can set breakpoints in stored procedures, pause the program execution process, and check the values ​​of program variables. Debugging stored procedures using the DBMS_DEBUG package requires the following steps:

1. Set compilation options

First, we need to set compilation options in the stored procedure so that the DBMS_DEBUG package can be used. Add the following statement before the code of the stored procedure:

ALTER SESSION SET PLSQL_DEBUG=TRUE;

2. Start the debugger

Next, we need to start debugging in the stored procedure device. Add the following code to the stored procedure:

DBMS_DEBUG_DBMS_DEBUG.CONNECT_SESSION;

This code will start the debugger and wait for the client to connect.

3. Connect to the client

Now, we need to connect to the database in the client and connect to the debugger we just started. In SQL Plus, you can use the following command to connect:

EXEC DBMS_DEBUG_JDWP.CONNECT_TCP(‘localhost’,4000);

If the connection is successful, "connected" will be prompted.

4. Set breakpoints

After the client connection is successful, we can set breakpoints in the stored procedure. Use the following code to set a breakpoint in the stored procedure:

DBMS_DEBUG_JDWP.BREAKPOINT(‘my_proc’,4);

This command will set a breakpoint on line 4 of the stored procedure. my_proc is the name of the stored procedure.

5. Execute the stored procedure

Now, we can execute the stored procedure and pause the program during execution to check the value of the variable.

Execute the following code in SQL Plus:

DECLARE
v_input NUMBER := 10;
v_output NUMBER;
BEGIN
my_proc(v_input, v_output);
END;

If the debugger is set up correctly, the program will pause at line 4. In the paused state, you can use the following command to check the value of the variable:

DBMS_DEBUG_JDWP.PRINT_VARIABLE('v_input');
DBMS_DEBUG_JDWP.PRINT_VARIABLE('v_output');

These two This command can display the values ​​of variables v_input and v_output.

6. Continue execution

After checking the variable value, if you still need to continue executing the program, you can use the following command:

DBMS_DEBUG_JDWP.CONTINUE;

This command will allow the program to continue executing until the next interrupt. point or the program ends.

Summary

In Oracle database, debugging stored procedures is a relatively common task. This article introduces two methods of debugging stored procedures, using SQL Developer and the DBMS_DEBUG package. No matter which method is used, you can easily set breakpoints in stored procedures, pause program execution, and check the values ​​of variables, helping developers quickly locate problems and fix bugs.

The above is the detailed content of How to debug stored procedures in Oracle. For more information, please follow other related articles on the PHP Chinese website!