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

Answer this question

OCI-22053: overflow error caused when retreiving valid data inserted using System.Data.OracleClient

koloo
Thanks Moustafa, rounding worked fixed this error. I added the round function to my query and the overflow error went away. .NET's version of double is not quite the same as Oracle's double precision data type.

Sam
mobildev
That seems like a potential solution to the problem but its difficult to convert oracle types back to system types. Will report back if I find an answer..
john gm
Dan, your problem is that you're trying to read the data into a DataSet, which doesn't support the full precision of the Oracle NUMBER data type, by default.

In ADO.NET 2.0, you can set the ReturnProviderSpecificTypes property of the OracleDataAdapter to true and have it read OracleNumber's instead of System.Decimal, which can't handle the value.
yokee01

I merely want to retrive and display the result of the sql statement:

select 1/3 from dual

but the dotnet framework throw exception.

How can I truncate the inexistent column

Maybe I can use

select trun(1/2,6) from dual

to void the exception,but I need a perfect solution.
ctg_demo

The problem is in the very 'descriptive' error "Overflow Occurred". This doesn't really SAY anything, but does give one a clue. After attempting to set the property mentioned above, I realized that our facility is not using ADO.NET v2.0, so that doesn't help. And one still gets this error using the previous version of ADO.NET.

So, what you do is create the target table's column with LESS bytes. The default in Oracle for a Float data column, is 126 bytes in length. Cut this way down and the problem is resolved. I will have to run more specific tests to determing what the actual number is, but don't let that stop you from trying it.

And NEVER use default NUMBER columns as the driver has incompatibility between Microsoft and Oracle. Like we didn't know that would happen ;)

Talons
Emerson Tadeu

Using Round or TRUNC functions fix the problem.

Thanks to all.
chellios
I've had this problem as well. I needed a more automatic solution since my application contains many existing queries loaded from a metadata table. In case anyone is interested, here's how I solved it.

I created a little OracleDataReader wrapper which implements the IDataReader interface and intercepts all function calls like GetValue etc. and does the following:

1. Call GetValue on the original reader object
2. If there is an OracleException with Code=22053 in the GetValue call, then get the number as an OracleNumber and round it to fit in a Decimal which as much precision intact as possible, like this:

n := FReader.GetOracleNumber(i);
if not n.Equals(OracleNumber.Zero) then
begin
exponent := Convert.ToInt32(Math.Floor(Double(

OracleNumber.Log10(OracleNumber.Abs(n)))));
if exponent < 0 then
exponent := 0;
if exponent > 26 then
exponent := 26;
n := OracleNumber.Round(n, 26 - exponent);
end;
result := Decimal(n);

With said wrapper it is possible to then create your own OracleDataAdapter that automatically does this rounding for you. Most of it is just boilerplate code, since you cannot inherit OracleDataAdapter, at least in v1.1.

Please note that you are in some sense losing information since you are rounding the number to fit in a Decimal. In my case, that's acceptable.

Btw, I unfortunately cannot post the full code, so please don't ask for it.
non ce
I have tried to set the ReturnProviderSpecificTypes property of the OracleDataAdapter to true,the result of dataset display NULL after fill the dataset using OracleDataAdapter.Fill(DataSet) method.
Freeman Shen
So, is there a fuller example of using ReturnProviderSpecificTypes Especially the conversion from an Oracle Number to a System.Decimal !
OCI-22053: overflow error caused when retreiving valid data inserted using System.Data.OracleClient

Answer this question