how to clear Oracle.ManagedDataAccess.Client table structure cache

Question

Here's an ASP.NET MVC Core action:

public ActionResult<object> Try()
{
    OracleConnection conn = (OracleConnection)_context.Database.GetDbConnection();
    if (conn.State != ConnectionState.Open)
        conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "select * from mytable fetch first 1 rows only";

    var result = new List<string>();
    using (OracleDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
            result.Add(reader.GetName(0)+": "+reader.GetValue(0).ToString());
    }
    return string.Join(',',result);
}

Then do the following:

1 create and fill table:

create table mytable (f1 number(10), f2 varchar2(10));
insert into mytable values (32,'32');
grant select on mytable to myuser;

2 call the Action:

http://localhost/MyController/Try
// output is okay:
// F1: 32

3 then:

alter table mytable add f3 varchar2(5) default ('f3') null;

4 call Action again:

http://localhost/MyController/Try

This throws an exception in OracleCommand.ExecuteReader:

System.Exception
  HResult=0x80131500
  Message=Internal Error
  Source=Oracle.ManagedDataAccess
  StackTrace:
   at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 clientInitialLOBFetchSize, Int64 initialLOBFetchSize, Int64 initialJSONFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()

Restarting the application solves the problem. It seems that ManagedDataAccess caches table structure somewhat.

The aim is make it work without restarting the application. How to do that? Is it necessary to clear some caches somehow?

Answer 1

Thanks to Zastai, solution has been found: statement cache.

oracleCommand.AddToStatementCache = false;

makes it work even if table structure changed or table deleted/recreated.

Answer 2

I had exactly the same problem and solved it using the PurgeStatementCache method:

conn.PurgeStatementCache();

This has the benefit of allowing statements to be cached (for performance) at the cost of having to explicitly purge the cache when cache busting changes have occurred.