System.ArgumentException: Value does not fall within the expected range. Bug in ODP.Net? — oracle-tech

    Forum Stats

  • 3,708,688 Users
  • 2,241,109 Discussions
  • 7,840,539 Comments

Discussions

Howdy, Stranger!

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

System.ArgumentException: Value does not fall within the expected range. Bug in ODP.Net?

Trond EldeTrond Elde Posts: 5
edited August 2020 in ODP.NET

I am experimenting with ODP.Net Core and run into a problem that seem to be a bug in ODP.Net. I am using Nuget package Oracle.ManagedDataAccess.Core 2.19.80 and target framework netcoreapp3.1.

I have contstructed two testcases below that will demonstrate the problem:

using System;using System.Collections.Generic;using System.Text;using System.Data.Common;using Oracle.ManagedDataAccess.Client;namespace OracleTest{    class TestODPAccessors    {        static readonly string connectionstring = "Data Source=192.168.10.120/orcl;User ID=system;Password=oracle";        private readonly DbConnection con;        private void InitializeTest()        {            using (DbCommand cmd = con.CreateCommand())            {                cmd.CommandText = "drop user testuser cascade";                try                {                    cmd.ExecuteNonQuery();                }                catch (OracleException ex)                {                                    if (ex.Number!=1918) //  ORA-01918: user does not exist                        throw;                }            }            using (DbCommand cmd = con.CreateCommand())            {                cmd.CommandText = "create user testuser identified by duptest";                cmd.ExecuteNonQuery();            }            using (DbCommand cmd = con.CreateCommand())            {                cmd.CommandText = "GRANT UNLIMITED TABLESPACE TO testuser";                cmd.ExecuteNonQuery();            }        }        private void CreateTableAndInsert(string sqlcoldef, string insert_expr)        {            using (DbCommand cmd = con.CreateCommand())            {                cmd.CommandText = $"CREATE TABLE testuser.datatypetest(col {sqlcoldef} null)";                cmd.Prepare();                cmd.ExecuteNonQuery();            }                        using (DbCommand cmd = con.CreateCommand())            {                cmd.CommandText = $"insert into testuser.datatypetest values({insert_expr})";                cmd.Prepare();                cmd.ExecuteNonQuery();            }        }        private void QueryTable()        {            using (OracleCommand cmd = (OracleCommand)con.CreateCommand())            {                cmd.CommandText = "select col from testuser.datatypetest";                cmd.Prepare();                OracleDataReader reader = cmd.ExecuteReader();                reader.Read();                                Object value = reader.GetOracleValue(0);                Console.WriteLine($"Oracle value as string {value}");            }        }        public TestODPAccessors(DbConnection con)        {            this.con = con;        }        private void TestInt32()        {            InitializeTest();            CreateTableAndInsert("number(9)", "'123456789'");            QueryTable();        }        private void TestVarChar2()        {            InitializeTest();            CreateTableAndInsert("varchar2(6)", "'abcABC'");            QueryTable();        }        public static void QueriesThatWork()        {            Console.ForegroundColor = ConsoleColor.Green;            Console.WriteLine("TESTCASE 1: QUERIES USING SAME DATABASE CONNECTION");            Console.WriteLine("==================================================");            using (DbConnection con = new OracleConnection())            {                con.ConnectionString = connectionstring;                con.Open();                var odp_accessor = new TestODPAccessors(con);                odp_accessor.TestInt32();                odp_accessor.TestVarChar2();                con.Close();            }        }        public static void QueriesThatDontWork()        {            Console.ForegroundColor = ConsoleColor.Red;            Console.WriteLine("\n\nTESTCASE 2: QUERIES USING THEIR OWN DATBASE CONNECTION");            Console.WriteLine(    "======================================================");            using (DbConnection con = new OracleConnection())            {                con.ConnectionString = connectionstring;                con.Open();                var odp_accessor = new TestODPAccessors(con);                odp_accessor.TestInt32();                con.Close();            }            using (DbConnection con = new OracleConnection())            {                con.ConnectionString = connectionstring;                con.Open();                var odp_accessor = new TestODPAccessors(con);                odp_accessor.TestVarChar2();                con.Close();            }        }    }}

The output:

TESTCASE 1: QUERIES USING SAME DATABASE CONNECTION==================================================Oracle value as string 123456789Oracle value as string abcABCTESTCASE 2: QUERIES USING THEIR OWN DATBASE CONNECTION=======================================================Oracle value as string 123456789Unhandled exception. System.ArgumentException: Value does not fall within the expected range.   at Oracle.ManagedDataAccess.Types.OracleDecimal..ctor(Byte[] numBytes, Boolean bContainsLength)   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetOracleDecimal(Int32 i)   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetOracleValue(Int32 i)   at OracleTest.TestODPAccessors.QueryTable() in C:\Users\trond\Source\Spikes\OracleTest\TestODPAccessors.cs:line 76   at OracleTest.TestODPAccessors.TestVarChar2() in C:\Users\trond\Source\Spikes\OracleTest\TestODPAccessors.cs:line 100   at OracleTest.TestODPAccessors.QueriesThatDontWork() in C:\Users\trond\Source\Spikes\OracleTest\TestODPAccessors.cs:line 146   at OracleTest.Program.Main(String[] args) in C:\Users\trond\Source\Spikes\OracleTest\Program.cs:line 17

What is wrong?

/Trond Elde

Trond Elde

Best Answer

  • Alex Keh-OracleAlex Keh-Oracle Posts: 2,720 Employee
    edited August 2020 Accepted Answer

    The statement cache keeps a copy of the statement metadata. The expectation is schema objects remain static throughout the app lifetime. If DML occurs, then statement caching should be turned off for those statements.

    I was able to reproduce your issue. If you add "Statement Cache Purge=true" to your connection string, the problem will be resolved and Test Case 2 will execute successfully.

    For an actual app, you don't need to turn off statement caching at the pool level. You can set OracleCommand,AddToStatementCache=false for SQL against schema objects that will be changing during runtime.

    Trond EldeTrond Elde

Answers

  • Alex Keh-OracleAlex Keh-Oracle Posts: 2,720 Employee
    edited August 2020 Accepted Answer

    The statement cache keeps a copy of the statement metadata. The expectation is schema objects remain static throughout the app lifetime. If DML occurs, then statement caching should be turned off for those statements.

    I was able to reproduce your issue. If you add "Statement Cache Purge=true" to your connection string, the problem will be resolved and Test Case 2 will execute successfully.

    For an actual app, you don't need to turn off statement caching at the pool level. You can set OracleCommand,AddToStatementCache=false for SQL against schema objects that will be changing during runtime.

    Trond EldeTrond Elde
  • Trond EldeTrond Elde Posts: 5
    edited August 2020

    Thanks for the clarification!

Sign In or Register to comment.