System.IndexOutOfRangeException in Oracle.ManagedDataAccessCore (2.19.80)

4252326

Enviroment

• Platform: .NET Core 3.1 Console Application
• Oracle Nuget: Oracle.ManagedDataAccessCore (2.19.80)

Steps

1. Open a connection

2. Fill a data table by a query

3. Change the table's structure e.g. add a new column

4. Fill the datatable again, an System.IndexOutOfRangeException will be throwed

5. Notice that I've set the AddToStatementCache as false

Exception

System.IndexOutOfRangeException: 'Index was outside the bounds of the array.'This exception was originally thrown at this call stack:    Oracle.ManagedDataAccess.Client.OracleDataReader.GetMinSchemaTable()    Oracle.ManagedDataAccess.Client.OracleDataReader.IsFillReader.set(bool)    Oracle.ManagedDataAccess.Client.OracleDataAdapter.Fill(System.Data.DataTable[], int, int, System.Data.IDbCommand, System.Data.CommandBehavior)    System.Data.Common.DbDataAdapter.Fill(System.Data.DataTable)    OracleTest.Extensions.GetTableBaseSchema(Oracle.ManagedDataAccess.Client.OracleConnection, string) in Program.cs    OracleTest.Program.Main(string[]) in Program.cs

Code Example

using Oracle.ManagedDataAccess.Client;using System;using System.Data;namespace OracleTest{    public static class Extensions    {        public static OracleCommand CreateCommandEx(this OracleConnection connection)        {            var command = connection.CreateCommand();            command.BindByName = true;            command.AddToStatementCache = false;            command.InitialLONGFetchSize = -1;            return command;        }        public static DataTable GetTableBaseSchema(this OracleConnection oracleConnection, string tableName)        {            var sql = $"select * from \"{tableName}\" where rownum < 0";            using (var command = oracleConnection.CreateCommandEx())            {                command.CommandText = sql;                DataTable dataTable = new DataTable();                using (var adapter = CreateDataAdapter())                {                    adapter.SelectCommand = command;                    adapter.Fill(dataTable);                    return dataTable;                }            }        }        public static void AddColumn(this OracleConnection oracleConnection, string tableName, string columnName)        {            var sql = $"ALTER TABLE \"{tableName}\" ADD \"{columnName}\" nvarchar2(2000)  DEFAULT NULL NULL";            using (var command = oracleConnection.CreateCommandEx())            {                command.CommandText = sql;                command.ExecuteNonQuery();            }        }        private static OracleDataAdapter CreateDataAdapter()        {            return new OracleDataAdapter();        }    }    class Program    {        static void Main(string[] args)        {            var connectionStr = @YOUR CONENCTION STRING HERE;            var oracleConnection = new OracleConnection(connectionStr);            oracleConnection.Open();            // Execute query, make sure that the oldTable exists in your database.            oracleConnection.GetTableBaseSchema("oldTable");            // Alter table column            oracleConnection.AddColumn("oldTable", "c3");            // Execute query again, and an exception will be throwed.            oracleConnection.GetTableBaseSchema("oldTable");        }    }}

Mark Williams

Are you able to verify if this persists after setting "Metadata Pooling=false" in the connection string?

I'm wondering if this is a result of the cached metadata rather than the statement cache.

Regards,

Mark

Mark Williams

Are you able to verify if this persists after setting "Metadata Pooling=false" in the connection string?

I'm wondering if this is a result of the cached metadata rather than the statement cache.

Regards,

Mark

4252326

Great! I tried to add the "Metadata Pooling=false" tho the connection string, then all is ok.

But in my workflow, the connection string is from users which means I can't control it.

Is there another solution for the problem? I have tried the following ways:

1. Close and reopen the connection (Not work, the exception was still throwed.)

2. Call OracleConnection.ClearAllPools() before execute a command (Not work)

Alex Keh-Oracle

You can try appending to the connection string the user provides with the Metadata Pooling attribute or you can turn off connection pooling. That's the two things I can think of you could try.

4252326

@Mark Williams-Oracle @Alex Keh - Product Manager-Oracle Thank you. Finally, we accept append the Metadata Pooling attribute. In addition, there might be a better way, such as exposing a new interface that looks like "OracleConnection.ClearMetadataCache()".