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.
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();
}
}
}
}
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