OCI-22053: overflow error caused when retreiving valid data inserted using System.Data.OracleClient
See the following example of how to insert a valid numeric value (a Decimal) using the System.Data.OracleClient that cannot be retreived using the same System.Data.OracleClient.
It is apparently some sort of bit-wise translation error between the Oracle Client and the Microsoft Data Provider. This test was run with VisualStudio2003 and Oracle 9i Client v9.2.0.4.0 connecting to Oracle Server 9.2.0.4.0, all running on Windows 2000 SP4.
using System;
using System.Data;
using System.Data.OracleClient;
namespace OdpTest
{
/// <summary>
/// The following command line application code illustrates haw a value that was inserted
/// using the System.Data.OracleClient causes an overflow error when retreiving it using
/// the same System.Data.OracleClient.
/// </summary>
public class OverflowTest
{
private const string CONNECT_STRING = "USER ID=foo;PASSWORD=bar;DATA SOURCE=foobar";
[STAThread]
private static void Main(string[] args)
{
using (OracleConnection conn = new OracleConnection(CONNECT_STRING))
{
conn.Open();
try
{
using (OracleCommand createCmd = conn.CreateCommand())
{
createCmd.CommandText = "create table overflow_test (num NUMBER)";
createCmd.ExecuteNonQuery();
}
using (OracleCommand insertCmd = conn.CreateCommand())
{
insertCmd.CommandText = "insert into overflow_test (num) values (:a)";
OracleParameter p = insertCmd.CreateParameter();
p.ParameterName = "a";
p.Value = 61M / 3M; // The magic value (there are more than just this one)
p.Direction = ParameterDirection.Input;
p.DbType = DbType.Decimal;
insertCmd.Parameters.Add(p);
Console.Error.WriteLine("Storing value: " + p.Value.ToString());
insertCmd.ExecuteNonQuery();
}
using (OracleCommand selectCmd = conn.CreateCommand())
{
selectCmd.CommandText = "select * from overflow_test";
DataTable overflowTest = new DataTable("overflow_test");
DataColumn num = new DataColumn("num", typeof (decimal));
overflowTest.Columns.Add(num);
OracleDataAdapter oda = new OracleDataAdapter(selectCmd);
oda.Fill(overflowTest);
int i = 0;
foreach (DataRow row in overflowTest.Rows)
{
Console.Error.Write("Row[{0}]:", i);
for (int j = 0; j < row.Table.Columns.Count; j++)
{
Console.Error.Write(" {0}", row[j]);
}
Console.Error.WriteLine();
}
i++;
}
}
finally
{
using (OracleCommand deleteCmd = conn.CreateCommand())
{
deleteCmd.CommandText = "drop table overflow_test";
deleteCmd.ExecuteNonQuery();
}
}
}
}
}
}