- 3,715,756 Users
- 2,242,858 Discussions
- 7,845,559 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 467 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 417 SQLcl
- 42 SQL Developer Data Modeler
- 184.9K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 9 DevOps
- 3K QA/Testing
- 250 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
System.ArgumentException: Value does not fall within the expected range. Bug 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
Best 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.
Answers
-
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.
-
Thanks for the clarification!