PL/SQL Dev debug Oracle stored procedures, triggers, and functions

The following describes how to debug a function. Other debugging operations are the same.

　　1. debugging location

In general, there are two places to debug the program in pl/SQL Dev:

1). New-> test window:

2) Right-click the program to be debugged and choose test.

　　2. debugging process

1) enter the test window through the debugging position, for example:

Click position 1 (or press F9) to enter the debugger. Position 2 shows the input and output parameters declared by the program. If the parameter is input, enter the corresponding input values here, the output parameters are displayed after debugging.

2) Press F9 to enter the start modulation ,:

Among them: 1 ---- interrupt debugging, 2 ---- single-step debugging. The simple point is to execute the program step by step. The green arrow indicates that all programs have been run. If there is a breakpoint setting, you can also jump to the next breakpoint.

3) Click single-step debugging to enter the function,

Here, you can try single-step debugging and run the program step by step

4). set breakpoints,

In the text area, click the left-side border. If a row number is displayed, that is, the position of the row number,

Click the Green Arrow to directly run to the breakpoint setting.

5). Monitor variables,

The position 1 is the monitoring window. This window displays the variables you want to monitor. You can copy the variables you want to monitor to this window, you can also right-click the variable and choose add variable to monitor (Location 2 ).

Continue the single-step execution. You can see the changes in the value of the monitoring variable (location 3.

　　3. Permission issues (ORA-0131)

　　1) When debugging the program, the error is ORA-0131: Insufficient privileges,

The reason is that the user permission is insufficient because the debug connect session system permission is missing.

2) solution: log on to the database as a SYS user and perform the authorization operation:

SQL> grant DEBUG CONNECT SESSION to user_name;

Note: 1. Some netizens pointed out that the debug any procedure permission should also be granted. After testing, this permission does not need to be granted!
2. You can view the permission information in the session_privs table of the data dictionary (You need to log on with user_name ):

Copy to ClipboardReference: [www.veryhuo.com] SQL> conn scott/tiger;
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
12 rows selected