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

    Question

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

     

    Tuesday, June 21, 2005 8:02 PM

Answers

  • 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.

    Tuesday, October 11, 2005 5:16 PM

All replies

  • 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.

    Tuesday, October 11, 2005 5:16 PM
  • 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..
    Tuesday, February 7, 2006 9:33 AM
  • 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.
    Friday, September 1, 2006 8:48 AM
  • So, is there a fuller example of using ReturnProviderSpecificTypes?  Especially the conversion from an Oracle Number to a System.Decimal?!
    Monday, November 6, 2006 9:47 PM
  • 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

    Monday, November 27, 2006 1:22 PM
  • 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.

     

    Friday, August 3, 2007 8:09 AM
  • Using Round or TRUNC functions fix the problem.

     

    Thanks to all.

    Sunday, January 27, 2008 7:26 AM
  • 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.

    • Proposed as answer by zippy72 Thursday, August 11, 2011 1:44 PM
    Monday, February 11, 2008 10:49 AM
  • 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

     

    Tuesday, February 12, 2008 8:58 PM
  • Sorry for the late reply, but thought I'd put in my 2 cents.  I too was having the same problem, but chose instead to use the GetProviderSpecificValue which eliminated the overflow error and I was able to successfully add it to a listbox.  Here's my code:

    Code Snippet

     For shtFieldCntr = 1 To result.FieldCount() - 1
         If result.IsDBNull(shtFieldCntr) Then
             itmListItem.SubItems.Add("")
         Else
             itmListItem.SubItems.Add(result.GetProviderSpecificValue(shtFieldCntr).ToString)
         End If
     Next shtFieldCntr



    I hope this helps someone.
    Wednesday, March 12, 2008 6:45 PM
  • In my experience, unless there is a problem with a date value , this error occurs when a value has too many decimal places. So, any field where you are performing an arithmatic operation, such as division or multiplication, enclose that field within a "ROUND(Field,3)" type function. Keep adding the Round function to all value fields, until the error stops.

     

    Goodluck!

     

     

     

    Wednesday, May 28, 2008 5:25 PM
  • Code Snippet

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

     

     

     

    Hi Robert.

     

    Nice one! I had to change it a bit (to C#), but all-in-all - seems to work nicely!

     

    Thanx!

     

    Tuesday, November 4, 2008 9:58 AM
  • I think it is a bug in Microsoft Oracle Client. Try this:

    decimal dec = 49.112576666666666666666666667m;  
    OracleNumber ora_number = new OracleNumber(dec);  
    decimal other = (decimal)ora_number; // throws an exception 
    Wednesday, March 11, 2009 10:55 AM
  • I know this is an old post but for the benefit of those searching for this problem, here is the answer. I have two servers. One with Oracle 9.1 client installed and the other Oracle 10.2.1 installed. The server with 10.2.1 does not have this problem, the one with 9.1 does. I upgraded the older one and the problem went away.
    Monday, December 7, 2009 4:20 PM
  • Mulick,

     Can you Please post the c# code for this ?

    Thanks

    Harish

    Tuesday, May 18, 2010 1:11 PM
  • I had a similar problem, where I decided doing a cast: 
    CAST (my_field AS NUMBER (15,4)) AS my_field
    Thursday, July 8, 2010 8:35 PM