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"
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
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
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.
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.
