Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Trond EldeAug 21 2020 — edited Aug 21 2020

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 123456789

Oracle value as string abcABC

TESTCASE 2: QUERIES USING THEIR OWN DATBASE CONNECTION

=======================================================

Oracle value as string 123456789

Unhandled 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

This post has been answered by Alex Keh-Oracle on Aug 21 2020
Jump to Answer

Comments

Processing

Post Details

Added on Aug 21 2020
2 comments
3,602 views