# Debug Oracle PL/SQL code

Procedure: Enable the Database Tools and SQL plugin

This functionality relies on the  Database Tools and SQL plugin, which  is bundled and enabled in IntelliJ IDEA   by default. If the relevant features are not available, make sure that you did not disable the plugin.

> **Note:**
> Database Tools and SQL functionality support is limited in IntelliJ IDEA without the Ultimate subscription.

1. Press `Ctrl+Alt+S` (Windows), `⌘ Comma` (macOS), `⌘ Comma` (IntelliJ IDEA Classic (macOS)), `⌘ Comma` (macOS System Shortcuts), `Ctrl+Alt+S` (XWin), `Ctrl+Alt+S` (GNOME), `Ctrl+Alt+S` (KDE), `Ctrl+Alt+S` (Emacs), `Ctrl+Alt+S` (Sublime Text), `⌘ Comma` (Sublime Text (macOS)), `Ctrl+Alt+S` (NetBeans), `Ctrl+Alt+S` (Visual Studio), `⌘ Comma` (Visual Studio (macOS)), `Ctrl+Alt+S` (Eclipse), `⌘ Comma` (Eclipse (macOS)) to open settings and then select `Plugins`.

2. Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.

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

Procedure:

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

Alternatively, select one of the existing query consoles from Query Consoles list (`Ctrl+Shift+F10` (Windows), `⌘ ⇧ F10` (macOS), `⌘ ⇧ F10` (IntelliJ IDEA Classic (macOS)), `⌘ ⇧ F10` (macOS System Shortcuts), `Ctrl+Shift+F10` (XWin), `Ctrl+Shift+F10` (GNOME), `Ctrl+Shift+F10` (KDE), `Ctrl+Shift+F10` (Emacs), `Ctrl+Shift+F10` (Sublime Text), `⌘ ⇧ F10` (Sublime Text (macOS)), `Ctrl+Shift+F10` (NetBeans), `Ctrl+Shift+F10` (Visual Studio), `⌘ ⇧ F10` (Visual Studio (macOS)), `Ctrl+Shift+F10` (Eclipse), `⌘ ⇧ F10` (Eclipse (macOS))).

2. Type or paste your code in the query console.

3. Click the Execute button ![the Execute button](https://resources.jetbrains.com.cn/help/img/idea/2026.2/app.actions.execute.svg) or press `Ctrl+Enter` (Windows), `⌘ ⏎` (macOS), `⌘ ⏎` (IntelliJ IDEA Classic (macOS)), `⌘ ⏎` (macOS System Shortcuts), `Ctrl+Enter` (XWin), `Ctrl+Enter` (GNOME), `Ctrl+Enter` (KDE), `Ctrl+Enter` (Emacs), `Ctrl+Enter` (Sublime Text), `⌘ ⏎` (Sublime Text (macOS)), `Ctrl+Enter` (NetBeans), `Ctrl+Enter` (Visual Studio), `⌘ ⏎` (Visual Studio (macOS)), `Ctrl+Enter` (Eclipse), `⌘ ⏎` (Eclipse (macOS)) to run the procedure code.

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

![Create a PL/SQL object](https://resources.jetbrains.com.cn/help/img/idea/2026.2/pl_sql_debugging_create_procedure.png)

A code snippet of the procedure:

```SQL
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

Procedure:

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](https://resources.jetbrains.com.cn/help/img/idea/2026.2/pl_sql_debugging_compile_for_debug.png)

## Step 3. Debug PL/SQL program units

Procedure: 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   tool window ,  double-click the PL/SQL object that you [created](#step-1-create-a-pl-sql-object) and [compiled](#step-2-compile-a-pl-sql-object-with-the-debug-option) for debugging.

2. Click the Run Procedure button ![the Run Procedure button](https://resources.jetbrains.com.cn/help/img/idea/2026.2/database-plugin.icons.procedureRun.svg). If the session is not selected, select a session from the list. For more information about managing sessions, refer to [Sessions](managing-connection-sessions.html).

3. From the Execute Routine dialog, copy and paste the anonymous block to the query 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.

[Video](https://resources.jetbrains.com.cn/help/img/idea/2026.2/pl_sql_debugging_run_debug_in_anonymous_block.mp4)

A code snippet of the procedure:

```SQL
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;
/
```

Procedure: 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   tool window ,  double-click the package that you [created](#step-1-create-a-pl-sql-object) and [compiled](#step-2-compile-a-pl-sql-object-with-the-debug-option) 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 query consoles from Query Consoles list (`Ctrl+Shift+F10` (Windows), `⌘ ⇧ F10` (macOS), `⌘ ⇧ F10` (IntelliJ IDEA Classic (macOS)), `⌘ ⇧ F10` (macOS System Shortcuts), `Ctrl+Shift+F10` (XWin), `Ctrl+Shift+F10` (GNOME), `Ctrl+Shift+F10` (KDE), `Ctrl+Shift+F10` (Emacs), `Ctrl+Shift+F10` (Sublime Text), `⌘ ⇧ F10` (Sublime Text (macOS)), `Ctrl+Shift+F10` (NetBeans), `Ctrl+Shift+F10` (Visual Studio), `⌘ ⇧ F10` (Visual Studio (macOS)), `Ctrl+Shift+F10` (Eclipse), `⌘ ⇧ F10` (Eclipse (macOS))).

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

5. Click Debug.

[Video](https://resources.jetbrains.com.cn/help/img/idea/2026.2/pl_sql_debugging_run_debug_packages.mp4)

A code snippet of the package:

```SQL
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:

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

Procedure: 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 query consoles from Query Consoles list (`Ctrl+Shift+F10` (Windows), `⌘ ⇧ F10` (macOS), `⌘ ⇧ F10` (IntelliJ IDEA Classic (macOS)), `⌘ ⇧ F10` (macOS System Shortcuts), `Ctrl+Shift+F10` (XWin), `Ctrl+Shift+F10` (GNOME), `Ctrl+Shift+F10` (KDE), `Ctrl+Shift+F10` (Emacs), `Ctrl+Shift+F10` (Sublime Text), `⌘ ⇧ F10` (Sublime Text (macOS)), `Ctrl+Shift+F10` (NetBeans), `Ctrl+Shift+F10` (Visual Studio), `⌘ ⇧ F10` (Visual Studio (macOS)), `Ctrl+Shift+F10` (Eclipse), `⌘ ⇧ F10` (Eclipse (macOS))).

2. Type a query in the query console.

3. Place breakpoints in a trigger.

4. Click Debug.

[Video](https://resources.jetbrains.com.cn/help/img/idea/2026.2/pl_sql_debugging_run_debug_triggers.mp4)

A code snippet of the trigger:

```SQL
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
/
```

## Stepping modes

When you debug PL/SQL code, you can select between two stepping modes: Graceful and Native.

In Graceful mode, you can pause the session that you debug (the target session), set and remove breakpoints. If no valid breakpoints are set, the debugger steps through code on a line-by-line basis.

In Native mode, the debugger uses Oracle native debugging commands. You cannot pause the target session or manage breakpoints but you might experience a boost in performance in CPU-intensive operations (operations that include a lot of computations and loops). You can read more about Oracle debugging commands in [the official Oracle documentation](https://docs.oracle.com/database/121/ARPLS/d_debug.htm#ARPLS66129). If no valid breakpoints are set, the debugger executes the whole routine.

Procedure: Change the stepping mode

1. Open settings ( `Ctrl+Alt+S` (Windows), `⌘ Comma` (macOS), `⌘ Comma` (IntelliJ IDEA Classic (macOS)), `⌘ Comma` (macOS System Shortcuts), `Ctrl+Alt+S` (XWin), `Ctrl+Alt+S` (GNOME), `Ctrl+Alt+S` (KDE), `Ctrl+Alt+S` (Emacs), `Ctrl+Alt+S` (Sublime Text), `⌘ Comma` (Sublime Text (macOS)), `Ctrl+Alt+S` (NetBeans), `Ctrl+Alt+S` (Visual Studio), `⌘ Comma` (Visual Studio (macOS)), `Ctrl+Alt+S` (Eclipse), `⌘ Comma` (Eclipse (macOS))) and navigate to `Build, Execution, Deployment Tools | Debugger | Stepping`.

2. From the  Stepping mode  list, select the stepping mode that you need.

Procedure: Pause at the beginning of debuggable code

You can force the debugger to pause at the beginning of debuggable code. The place where the debugger pauses is detected automatically. If you assign variable values in the declaration section, the debugger pauses at the variable declaration. If no values are assigned to variables, the debugger skips the declaration section and pauses at the `BEGIN` keyword in the execution section.

1. Open settings ( `Ctrl+Alt+S` (Windows), `⌘ Comma` (macOS), `⌘ Comma` (IntelliJ IDEA Classic (macOS)), `⌘ Comma` (macOS System Shortcuts), `Ctrl+Alt+S` (XWin), `Ctrl+Alt+S` (GNOME), `Ctrl+Alt+S` (KDE), `Ctrl+Alt+S` (Emacs), `Ctrl+Alt+S` (Sublime Text), `⌘ Comma` (Sublime Text (macOS)), `Ctrl+Alt+S` (NetBeans), `Ctrl+Alt+S` (Visual Studio), `⌘ Comma` (Visual Studio (macOS)), `Ctrl+Alt+S` (Eclipse), `⌘ Comma` (Eclipse (macOS))) and navigate to `Build, Execution, Deployment Tools | Debugger | Stepping`.

2. Select Pause at begin.

![Pause at the beginning of debuggable code](https://resources.jetbrains.com.cn/help/img/idea/2026.2/db_pause_at_debuggable_code.svg)