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