Oracle ORA-04068, ORA-04061, ORA-04065 Errors when Making Changes to Package

[CavidH]

Hello,

I am encountering errors while interacting with an Oracle database from C# using pooling true connections. Specifically, when I make changes to a package in Oracle, I receive the following errors on the C# side:

ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "TEST_APP.TEST" has been invalidated ORA-04065: not executed, altered or dropped package body "TEST_APP.TEST.TEST" ORA-06508: PL/SQL: could not find program unit being called: "TEST_APP.TEST.TEST" ORA-06512: at line 1 However, when I retry the same query or call to the package, the errors do not occur. This inconsistency is causing instability in my application.

I am using pooling true connections from C# to Oracle to manage database connections efficiently. The issue seems to arise only on the initial call to the package.

Has anyone encountered similar issues with Oracle packages in a C# environment? What could be causing these errors initially, and how can I ensure consistent behavior when calling Oracle packages from C#?

Thank you for your help!

[alexkeh]

By default, ODP.NET caches PL/SQL metadata and parsing info for procedures and packages it has executed before during the connection's lifetime. This makes performance faster as the same statement executed doesn't need be re-parsed every time nor its metadata re-retrieved every time.

The expectation is that the PL/SQL will not change during the connection's lifetime for that metadata and parse info to remain valid.

If the package/procedure is expected to change during the app's lifetime, then I would recommend turning off metadata pooling and statement caching. You can turn these off by setting Metadata Pooling and Self Tuning to false on the connection string. Note that this will also turn off these performance features for all other statement executions as well.

[CavidH]

Can we ensure the re-fetching of metadata with each query by setting polling to true?

[CavidH]

By default, ODP.NET caches PL/SQL metadata and parsing info for procedures and packages it has executed before during the connection's lifetime. This makes performance faster as the same statement executed doesn't need be re-parsed every time nor its metadata re-retrieved every time.

The expectation is that the PL/SQL will not change during the connection's lifetime for that metadata and parse info to remain valid.

If the package/procedure is expected to change during the app's lifetime, then I would recommend turning off metadata pooling and statement caching. You can turn these off by setting Metadata Pooling and Self Tuning to false on the connection string. Note that this will also turn off these performance features for all other statement executions as well.

I used this connection string, but it didn't work. var cs = "User Id=test_user;Password=test_pass34edgf;Data Source=user_dc;Validate Connection=True;Pooling=True;Metadata Pooling=False;Self Tuning=false;";

[alexkeh]

Then, it's unlikely to be an ODP.NET cache issue. The incorrect PL/SQL must be cached on the server side. Can you try executing DBMS_SESSION.RESET_PACKAGE after making the package changes? That should clear the PL/SQL package server cache of past execution values.

[CavidH]

Then, it's unlikely to be an ODP.NET cache issue. The incorrect PL/SQL must be cached on the server side. Can you try executing DBMS_SESSION.RESET_PACKAGE after making the package changes? That should clear the PL/SQL package server cache of past execution values.

The DBMS_SESSION.RESET_PACKAGE command did not help. It might be because this command runs within its own session, so it was not effective?

[CavidH]

the error I encountered link

[CavidH]

Then, it's unlikely to be an ODP.NET cache issue. The incorrect PL/SQL must be cached on the server side. Can you try executing DBMS_SESSION.RESET_PACKAGE after making the package changes? That should clear the PL/SQL package server cache of past execution values.

Based on my research, I have decided that since pooling is set to true, it seems like the connection keeps some sort of cache when it is first opened. Therefore, if changes are made to the package before the application runs, everything works fine. However, if changes are made to the package after the connection has been established in the pool, issues arise.

[alexkeh]

Yes, you should run the DBMS_SESSION.RESET_PACKAGE from the same session. You can do this from the same ODP.NET user connection the package is being run from.

One way to use this procedure is to add error handling to your package execution. If ODP.NET encounters and an ORA-04068, reset the package and execute again.

[CavidH]

Yes, you should run the DBMS_SESSION.RESET_PACKAGE from the same session. You can do this from the same ODP.NET user connection the package is being run from.

One way to use this procedure is to add error handling to your package execution. If ODP.NET encounters and an ORA-04068, reset the package and execute again.

I will test and report back

[CavidH]

Yes, you should run the DBMS_SESSION.RESET_PACKAGE from the same session. You can do this from the same ODP.NET user connection the package is being run from.

One way to use this procedure is to add error handling to your package execution. If ODP.NET encounters and an ORA-04068, reset the package and execute again.

This case was unsuccessful because there could be many connections in the pool.

[CavidH]

I found such a solution `public static class OracleCommandExtensions { public static OracleDataReader ExecuteReader(this OracleCommand cmd, params int[] errorCodes) { try { return cmd.ExecuteReader(); } catch (OracleException ex) when (Array.Exists(errorCodes, code => code == ex.Number)) { OracleConnection.ClearPool(cmd.Connection); return cmd.ExecuteReader(); } catch { throw; } }

public static void Fill(this OracleDataAdapter adapter, DataSet dataSet, params int[] errorCodes) { try { adapter.Fill(dataSet); } catch (OracleException ex) when (Array.Exists(errorCodes, code => code == ex.Number)) { OracleConnection.ClearPool(adapter.SelectCommand.Connection); adapter.Fill(dataSet); } catch { throw; } } public static int ExecuteNonQuery(this OracleCommand cmd, params int[] errorCodes) { int RowsAffected = 0;

    try
    {
        RowsAffected = cmd.ExecuteNonQuery();
    }
    catch (OracleException ex) when (Array.Exists(errorCodes, code => code == ex.Number))
    {
        OracleConnection.ClearPool(cmd.Connection);
        RowsAffected = cmd.ExecuteNonQuery();
    }
    catch
    {
        throw;
    }
    return RowsAffected;
}

}

`

[alexkeh]

I'm glad you found a solution that works for you.

[CavidH]

I'm glad you found a solution that works for you.

Thank you for your help.

[CavidH]

The ORA-04061 error in Oracle occurs when an existing state of a package has been invalidated due to changes in the package. This often happens when a package is recompiled or modified, leading to the need to refresh the package state in memory. Here’s a concise summary of the error and its implications:

Cause of the Error:

ORA-04061: The existing state of the package has been invalidated. ORA-04068: Existing state of packages has been discarded. ORA-04065: Package is not executed, altered, or dropped.

Error Scenario: --You have a PL/SQL package with global variables and functions. When you modify the package, such as adding default values or changing the package initialization code, Oracle invalidates the old state and loads the new version.

--This invalidation can affect dependent objects and sessions.

Behavior of Global Variables:

--Global variables are session-specific. Each session maintains its own copy of the global variable.

--Modifications to the package affect only the current session and will reset the state if the package is recompiled or altered.

Example Scenario:

--You have a package Test_Pkg with functions to set and get a global variable. Modifying the package (e.g., adding a default value) results in ORA-04061 errors in sessions using the old version.

--Dependent objects (like functions or procedures that use the package) may need to be recompiled, and existing sessions might show outdated results until the package is refreshed.

Handling the Error:

--Client-Side Exception Handling: Design your client application to handle these exceptions gracefully. For example, trap the runtime error and retry the operation if the package state is invalidated.

--Avoid Storing Critical State Information: Do not rely on PL/SQL global variables for critical or volatile state information that cannot be easily reconstructed or reset.

--Session Management: Inform users to disconnect before package upgrades if possible, to avoid disruptions.

The PRAGMA SERIALLY_REUSABLE directive indicates that the package's state can be reset for each session. This pragma helps ensure that package resources are cleanly reinitialized for each session, thus avoiding stale data issues. However, you should be aware that:

Usage: While PRAGMA SERIALLY_REUSABLE is helpful for managing package state across sessions, it is important to understand its implications and ensure that your application design accommodates this behavior. Session-Specific Data: The pragma ensures that global variables and other stateful elements are reset for each new session, which can impact how data is managed and accessed. This explanation highlights the utility of PRAGMA SERIALLY_REUSABLE while also noting the need for careful consideration in application design.

Client-Side Exception Handling Example

Links

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SERIALLY_REUSABLE-pragma.html

https://oraclequirks.blogspot.com/2007/03/ora-04061-existing-state-of-package-has.html#google_vignette