DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle AI Database 26ai: RESETTABLE Clause

#devops #oracle #26ai #oracle26ai

In Oracle, PL/SQL packages can maintain a session-level state. This means that once a package is loaded into a user session, its global variables retain values across multiple calls. In previous Oracle versions, when a package was recompiled, all active sessions holding that package’s state would encounter ORA-04068 errors.

In Oracle 26ai, the RESETTABLE clause introduces a new way to manage PL/SQL package states more safely and efficiently. To understand how it works, let’s examine the following practical scenario.

1. Default Package Behavior (Without RESETTABLE)

First, we create a simple package with a global variable and a procedure that increments and prints its value:

CREATE OR REPLACE PACKAGE my_pkg AS
  Glob_cnt number := 0;
  PROCEDURE prc_inc_cnt;
END my_pkg;
/
Package created
Enter fullscreen mode Exit fullscreen mode 
CREATE OR REPLACE PACKAGE BODY my_pkg AS
  PROCEDURE prc_inc_cnt IS
  BEGIN
    Glob_cnt := Glob_cnt + 1;
    DBMS_OUTPUT.PUT_LINE('Counter = ' || Glob_cnt);
  END;
END my_pkg;
/
Package body created
Enter fullscreen mode Exit fullscreen mode

Now, let’s observe how it behaves across two sessions.

session 1:

SQL>  set serveroutput on

SQL> exec my_pkg.prc_inc_cnt;
Counter = 1

SQL> exec my_pkg.prc_inc_cnt;
Counter = 2
Enter fullscreen mode Exit fullscreen mode

Session 2: Recompile the package

SQL> alter package my_pkg compile;
Package altered
Enter fullscreen mode Exit fullscreen mode

Back to Session 1:

SQL> exec my_pkg.prc_inc_cnt;
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "VAHID.MY_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package "VAHID.MY_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "VAHID.MY_PKG"
ORA-06512: at line 1
Enter fullscreen mode Exit fullscreen mode

The session fails because Oracle automatically discards the package state when the package is recompiled in another session. The next call reinitializes the package:

Session 1:

SQL>  set serveroutput on
SQL> exec my_pkg.prc_inc_cnt;
Counter = 1

SQL> exec my_pkg.prc_inc_cnt;
Counter = 2
Enter fullscreen mode Exit fullscreen mode

2. Using the RESETTABLE Clause

Now let’s modify the package to use the RESETTABLE clause. The RESETTABLE clause must be declared directly in the CREATE PACKAGE statement:

CREATE OR REPLACE PACKAGE my_pkg RESETTABLE AS
  Glob_cnt number := 0;
  PROCEDURE prc_inc_cnt;
END my_pkg;
/
Package created
Enter fullscreen mode Exit fullscreen mode 
CREATE OR REPLACE PACKAGE BODY my_pkg RESETTABLE AS
  PROCEDURE prc_inc_cnt IS
  BEGIN
    Glob_cnt := Glob_cnt + 1;
    DBMS_OUTPUT.PUT_LINE('Counter = ' || Glob_cnt);
  END;
END my_pkg;
/
Package body created
Enter fullscreen mode Exit fullscreen mode

In the following, we will repeat the same scenario to see how the behavior differs when the package is created with the RESETTABLE clause.

Session 1:

SQL> set serveroutput on

SQL> exec my_pkg.prc_inc_cnt;
Counter = 1

SQL> exec my_pkg.prc_inc_cnt;
Counter = 2
Enter fullscreen mode Exit fullscreen mode

Session 2: Recompile the package:

SQL> ALTER PACKAGE my_pkg COMPILE;
Package altered
Enter fullscreen mode Exit fullscreen mode

Back to Session 1:

SQL> exec my_pkg.prc_inc_cnt;
Counter = 1

SQL> exec my_pkg.prc_inc_cnt;
Counter = 2
Enter fullscreen mode Exit fullscreen mode

No ORA-04068 error appears this time. The package automatically resets itself, and the state restarts cleanly from zero.

In conclusion, the RESETTABLE clause solves this by allowing Oracle to safely discard and reinitialize the package’s state without raising an error. When the package detects that its state is no longer valid, it resets automatically rather than crashing the session.

Top comments (0)