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.

Key Takeaways from "Oracle AI Foundations Associate" Training

I am pleased to share that I have successfully completed the Oracle AI Foundations Associate training and Certification. This program provided a comprehensive introduction to the world of Artificial Intelligence (AI) from foundational theory to hands-on exploration with Oracle Cloud Infrastructure (OCI) AI services all underscored by the importance of Responsible AI.

1. Core AI Concepts Clarified

The training demystified the distinctions and relationships between Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL).
It also deepened my understanding of major ML types, including:
Supervised Learning – for predictive modeling, regression, and classification.
Unsupervised Learning – for discovering structure and patterns within data.

This foundation helped translate theoretical AI ideas into clear, real-world applications.

2. Generative AI and Large Language Models

A major focus was the rapidly advancing field of Generative AI and Large Language Models (LLMs).
The course emphasized that effective Prompt Engineering is key to obtaining accurate, context-aware outputs. I also explored Retrieval-Augmented Generation (RAG) — a technique that enables LLMs to integrate proprietary and up-to-date enterprise data, making them highly relevant for business use cases.

3. AI in the Enterprise Cloud: The OCI Advantage

Oracle’s approach to AI through OCI stood out as both practical and scalable.

Key components of the portfolio include:

OCI AI Services – pre-built and customizable models such as OCI Vision and OCI Language for image and text analysis.
OCI Generative AI Service – a managed platform offering access to foundational LLMs and fine-tuning capabilities.
OCI Data Science – an environment supporting the full lifecycle of model development, training, and deployment.

This structure clearly illustrates how enterprises can implement AI seamlessly within existing cloud ecosystems.

Building on Responsible AI

Beyond technology, the program emphasized the importance of trustworthy and ethical AI.
Key principles include:
Fairness – identifying and mitigating bias in data.
Transparency (Explainable AI) – ensuring clarity behind AI-driven decisions.
Accountability and Robustness – maintaining reliability, governance, and security.

These pillars ensure that AI adoption remains ethical, credible, and sustainable across industries.

What’s Next

Earning this certification marks an important milestone in my AI learning journey. I now have the foundational knowledge to not only discuss AI confidently but also to apply OCI’s AI tools responsibly in real-world contexts.

I’m looking forward to leveraging these insights on upcoming projects and continuing toward advanced Oracle AI certifications.

For anyone looking to build a strong, industry-recognized foundation in Artificial Intelligence, I highly recommend exploring the Oracle AI training and certification path, it’s a great way to connect theory with practical, cloud-based innovation.

Thanks & Regards,

https://oracleracexpert.com