System.IndexOutOfRangeException in Oracle.ManagedDataAccessCore (2.19.80) — oracle-tech

    Forum Stats

  • 3,708,736 Users
  • 2,241,116 Discussions
  • 7,840,565 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

System.IndexOutOfRangeException in Oracle.ManagedDataAccessCore (2.19.80)

42523264252326 Posts: 7
edited July 2020 in ODP.NET

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");        }    }}
4252326

Best Answer

  • Mark WilliamsMark Williams Posts: 66
    edited July 2020 Accepted Answer

    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

    Alex Keh-Oracle42523264252326

Answers

  • Mark WilliamsMark Williams Posts: 66
    edited July 2020 Accepted Answer

    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

    Alex Keh-Oracle42523264252326
  • 42523264252326 Posts: 7
    edited July 2020

    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-OracleAlex Keh-Oracle Posts: 2,720 Employee
    edited July 2020

    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.

  • 42523264252326 Posts: 7
    edited July 2020

    @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()".

Sign In or Register to comment.