Avoiding ORA-04068 Using RESETTABLE Packages in Oracle 26ai

In Oracle prior Oracle 26ai, if a PL/SQL package is loaded the variable values are retained across multiple calls in the same session. That means the package will keep their state within a user session. However, if a package was recompiled or modified all the active sessions that has the package will receive ORA-04068 errors.

The RESETTABLE clause introduced in Oracle 26ai, it is beneficial to avoid -ORA04068 error when the existing state of the package has been discarded.

The RESETTABLE clause can be used in package or package body during the creation. But note that users cannot be able to use RESETTABLE clause in combination with the SERIALLY_REUSABLE pragma.

Syntax: - CREATE OR REPLACE PACKAGE [BODY] RESETTABLE

Using below sample package and package body for demonstration

SQL> CREATE OR REPLACE PACKAGE sales_pkg AS
g_total_sales NUMBER := 0;
PROCEDURE add_sale;
END sales_pkg;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY sales_pkg AS
PROCEDURE add_sale IS
BEGIN
g_total_sales := g_total_sales + 100; -- adds a fixed sale amount
DBMS_OUTPUT.PUT_LINE('Total Sales = ' || g_total_sales);
END;
END sales_pkg;
/
Package body created

We will use two different sessions for demonstration

Session 1: execute the package
SQL> EXEC sales_pkg.add_sale;

Total Sales = 100

Session 2: recompile the package

SQL> ALTER PACKAGE sales_pkg COMPILE;
Package altered

Return to Session 1:

SQL> EXEC sales_pkg.add_sale;

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SALES_PKG.ADD_SALE" has been invalidated
ORA-04065: not executed, altered or dropped package "SALES_PKG.ADD_SALE"
ORA-06508: PL/SQL: could not find program unit being called: "SALES_PKG.ADD_SALE"

....

The main reason you are receiving this error because Oracle discards the package state when it is recompiled. When user run next time, it will reinitialize the package.

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

SQL> EXEC sales_pkg.add_sale;
Total Sales = 200

Now we will modify our sample code with RESETTABLE clause

SQL> CREATE OR REPLACE PACKAGE sales_pkg RESETTABLE AS
g_total_sales NUMBER := 0;
PROCEDURE add_sale;
END sales_pkg;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY sales_pkg RESETTABLE AS
PROCEDURE add_sale IS
BEGIN
g_total_sales := g_total_sales + 100; -- adds a fixed sale amount
DBMS_OUTPUT.PUT_LINE('Total Sales = ' || g_total_sales);
END;
END sales_pkg;
/
Package body created

We will repeat the same exercise to observe how the RESETTABLE clause affects the package’s behavior.

Session 1: execute the package

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

Session 2: recompile the package

SQL> ALTER PACKAGE sales_pkg COMPILE;
Package altered

Return to Session 1: execute the package again

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

SQL> EXEC sales_pkg.add_sale;
Total Sales = 200

In the second scenario, when Oracle detects that a package’s state is no longer valid, it automatically reinitializes the package instead of raising an ORA-04068 error. By using the RESETTABLE clause, the package can safely discard its state and reinitialize without causing any errors to user session.