DataGrip 2023.2 Help

Debugging

The debugger helps you to find errors in your code by investigating the runtime behavior of the code. With the debugger in DataGrip, you can go through the code line by line, step in and out of called routines, evaluate expressions, and watch variables as they change their values.

Debugging Oracle PL/SQL code

The debugger is based on the Oracle Probe that uses API of the DBMS_DEBUG package and should work on Oracle servers 9.0 and later.

In Oracle, you can debug the following program units (PL/SQL programs): anonymous blocks, packages, procedures, functions, and triggers.

Step 1. Create a PL/SQL object

  1. Right-click the Oracle data source and select New | Query Console.

    Alternatively, select one of the existing consoles from Query Consoles list (Control+Shift+F10).

  2. Type or paste your code in the console.

  3. Click the Execute button the Execute button or press Control+Enter to run the procedure code.

    As a result, you see a created object in the Database Explorer ( View | Tool Windows | Database Explorer).

Create a PL/SQL object

A code snippet of the procedure:

CREATE PROCEDURE simpleprocedure (inval NUMBER) IS tmpvar NUMBER; tmpvar2 NUMBER; total NUMBER; BEGIN tmpvar := 0; tmpvar2 := 0; total := 0; FOR lcv IN 1 .. inval LOOP total := 2 * total + 1 - tmpvar2; tmpvar2 := tmpvar; tmpvar := total; END LOOP; DBMS_OUTPUT.put_line ('TOTAL IS: ' || total); END simpleprocedure; /

Step 2. Compile a PL/SQL object with the debug option

To enable debugging for a PL/SQL code, you need to compile it with the DEBUG option. The process of compilation converts PL/SQL code to Pro*C, which is then compiled to Oracle shared libraries. The compilation helps the Oracle interpreter to process your code faster.

  1. Right-click the PL/SQL object that you want to debug and select SQL Scripts | Recompile.

  2. In the Recompile dialog, select With DEBUG option.

  3. Click OK.

    Compile code for debugging

Step 3. Debug PL/SQL program units

Debug PL/SQL procedures and functions through anonymous blocks

PL/SQL program units organize the code into blocks. A block without a name is an anonymous block. The anonymous block is not stored in the Oracle database. In the debugging process, you use the anonymous block to pass values for parameters.

To debug procedures, packages, and functions, write an anonymous block that calls the necessary routine.

  1. In the Database Explorer ( View | Tool Windows | Database Explorer) , double-click the PL/SQL object that you created and compiled for debugging.

  2. Click the Run Procedure button the Run Procedure button. If the session is not selected, select a session from the list. For more information about managing sessions, refer to Sessions.

  3. From the Execute Routine dialog, copy and paste the anonymous block to the console.

  4. Place breakpoints in the anonymous block and in the PL/SQL program object that is referenced in this anonymous block.

  5. (Optional) Modify parameter values.

  6. Click Debug.

A code snippet of the procedure:

CREATE PROCEDURE simpleprocedure (inval NUMBER) IS tmpvar NUMBER; tmpvar2 NUMBER; total NUMBER; BEGIN tmpvar := 0; tmpvar2 := 0; total := 0; FOR lcv IN 1 .. inval LOOP total := 2 * total + 1 - tmpvar2; tmpvar2 := tmpvar; tmpvar := total; END LOOP; DBMS_OUTPUT.put_line ('TOTAL IS: ' || total); END simpleprocedure; /

Debug PL/SQL packages through anonymous blocks

A package is a schema object that groups logically related PL/SQL types, items, and subprograms.

Just like with procedures and functions, to debug a package, write the anonymous block that calls the necessary routine.

  1. In the Database Explorer ( View | Tool Windows | Database Explorer) , double-click the package that you created and compiled for debugging.

  2. Place breakpoints in the package.

  3. Right-click the Oracle data source and select New | Query Console.

    Alternatively, select one of the existing consoles from Query Consoles list (Control+Shift+F10).

  4. In the Oracle console, write an anonymous block that triggers the procedure.

  5. Click Debug.

A code snippet of the package:

CREATE PACKAGE PKG_DBGD AS FUNCTION tst_1(I IN INTEGER) RETURN INTEGER; FUNCTION tst_2(I IN INTEGER) RETURN INTEGER; END PKG_DBGD; / CREATE PACKAGE BODY PKG_DBGD AS FUNCTION tst_1(I IN INTEGER) RETURN INTEGER IS BEGIN IF I BETWEEN 5 AND 10 THEN RETURN 2 * I; END IF; IF I BETWEEN 0 AND 4 THEN RETURN tst_2(3 + I); END IF; IF I BETWEEN 6 AND 10 THEN RETURN tst_2(I - 2); END IF; RETURN I; END TST_1; FUNCTION tst_2(I IN INTEGER) RETURN INTEGER IS BEGIN IF I BETWEEN 6 AND 8 THEN RETURN tst_1(I - 1); END IF; IF I BETWEEN 1 AND 5 THEN RETURN I * 2; END IF; RETURN I - 1; END TST_2; END PKG_DBGD; /

A code snippet of the anonymous block:

DECLARE V_RESULT INTEGER; BEGIN V_RESULT := PKG_DBGD.tst_1(4); END;

Debug PL/SQL triggers through queries

A trigger is a PL/SQL program unit that is automatically called by the DBMS when you issue INSERT, UPDATE or DELETE queries. Triggers are associated with a table and are called before or after you insert, update, or delete a data row. A table can have several triggers.

To debug a trigger, write an INSERT, UPDATE or DELETE query to a table or a view.

  1. Right-click the Oracle data source and select New | Query Console.

    Alternatively, select one of the existing consoles from Query Consoles list (Control+Shift+F10).

  2. Type a query in the console.

  3. Place breakpoints in a trigger.

  4. Click Debug.

A code snippet of the trigger:

create table Table_with_Triggers ( Code char(1), Name varchar(26) ) / create trigger Table_with_Triggers_Trg_1 before update on Table_with_Triggers declare x positive := 42; begin dbms_output.put_line('trigger before update statement'); end; / create trigger Table_with_Triggers_Trg_2 before update on Table_with_Triggers for each row begin dbms_output.put_line('trigger before update row '||:old.Code); end; / create trigger Table_with_Triggers_Trg_3 after update on Table_with_Triggers for each row begin dbms_output.put_line('trigger after update row '||:old.Code); end; / create trigger Table_with_Triggers_Trg_4 after update on Table_with_Triggers begin dbms_output.put_line('trigger after update statement'); end; / insert into Table_with_Triggers values ('A', 'Altai'); insert into Table_with_Triggers values ('B', 'Barnaul'); commit; alter trigger Table_with_Triggers_Trg_1 compile debug; alter trigger Table_with_Triggers_Trg_2 compile debug; alter trigger Table_with_Triggers_Trg_3 compile debug; alter trigger Table_with_Triggers_Trg_4 compile debug; begin dbms_output.enable(4000); end; / begin update Table_with_Triggers set Name = Name || '+'; end; / update Table_with_Triggers set Name = Name || '+' / rollback / select * from user_source /

Managing debugger sessions

Pause/Resume a debugger session

When the debugger session is running, you can pause/resume it as required using the buttons on the toolbar of the Debug tool window:

  • To pause a debugger session, click the Pause button.

  • To resume a debugger session, click the Resume button F9.

Restart a debugger session

  • Click the Rerun button in the Debug tool window or press Control+F5.

    the Rerun button

Terminate a debugger session

  • Click the Stop button in the Debug tool window. Alternatively, press Control+F2 and select the process to terminate (if there are two or more of them).

    the Stop button

Configuring debugger settings

  • In the Settings dialog (Control+Alt+S), navigate to Tools | Debugger.

Useful debugger shortcuts

Action

Hotkey

Toggle breakpoint

Control+F8

Resume program

F9

Step over

F8

Step into

F7

Stop

Control+F2

View breakpoint details/all breakpoints

Control+Shift+F8

Debug code at caret

Shift+F9

Last modified: 29 September 2023
Run anything Breakpoints