1 2 Previous Next 15 Replies Latest reply: Nov 2, 2005 9:30 AM by 449052 RSS

    Upgrade ODP.NET, strange problem

    373681
      I upgraded the testserver 1 1/2 weeks ago, and we have had no problems. I did the production server on friday.
      Now, initially all seemed well. The problem with ODP.NET returning garbage was still there, but this time it was fixable by adding the rowid.

      There was another bug however which seem different.

      System.OverflowException: Arithmetic operation resulted in an overflow.
      at Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx)
      at Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
      at Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)
      at Oracle.DataAccess.Client.OracleDataReader.GetValues(Object[] values)
      at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges)
      at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
      at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
      at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
      at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
      at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
      at test.clsSelection.s_selection() in C:\webdata\talk2me.no\h\hafslundnet-services\test.asmx:line 233

      This table has five rows in it, all records with whole numbers below 523.
      First, I tried restricting the number of rows, and it turned out that row 3 was the problem. Then I started updating record 3 with record 2 info, column by column. When the last column on record 3 was updated, it worked. I updated row 3 with the old info again, and then everything was working as expected.

      Any ideas of what the cause may be?

      Morten
        • 1. Re: Upgrade ODP.NET, strange problem
          340203
          We can look into more details if you can provide us the test case.

          Thanks
          Martha
          • 2. Re: Upgrade ODP.NET, strange problem
            373681
            If I could I would. I have tried, but there is no way I can think of to reproduce the error now.

            • 3. Re: Upgrade ODP.NET, strange problem
              373681
              I do get the error from time to time:
              Heres the error:
              System.OverflowException: Arithmetic operation resulted in an overflow.
              at Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx)
              at Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
              at Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)
              at Oracle.DataAccess.Client.OracleDataReader.GetValues(Object[] values)
              at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges)
              at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
              at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
              at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
              at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
              at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
              at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
              at test.clsSelection.s_selection() in C:\webdata\talk2me.no\h\hafslundnet-services\test.asmx:line 233



              Heres the asmx code:

              public DataSet s_selection()
                        {

                                            OracleCommand objCmd = new OracleCommand();
                             objCmd.CommandText = "SELECTION_INT.s_selection";
                             objCmd.CommandType = CommandType.StoredProcedure;
                             objCmd.Connection = currentConnection;
                             OracleDataAdapter objAdapter = new OracleDataAdapter();
                             objCmd.Parameters.Add(new OracleParameter("selection", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;               bool closeSelf = OpenConnection();
                             DataSet ds = new DataSet();
                             ds.DataSetName = "s_selection";
                             objAdapter = new OracleDataAdapter(objCmd);
                             objAdapter.Fill(ds);
                             ds.Tables[0].TableName = "selection";
                             objCmd = null;
                             if (closeSelf) CloseConnection();
                             objAdapter = null;
                             return ds;
                             
                        }

              Heres the plsql code:
              procedure s_selection(t_selection out sys_refcursor) is
              begin
              open t_selection for
              select s.*
              from selector s;
              end;

              Heres the table def:
              -- Create table
              create table SELECTOR
              (
              SELECTOR_ID NUMBER not null,
              DESCRIPTION VARCHAR2(4000) not null,
              LAST_COUNT NUMBER,
              LAST_COUNT_DATE DATE,
              LAST_COUNT_DURATION NUMBER,
              SELECTOR_PARENT_ID NUMBER
              )
              tablespace ORA_DATA
              pctfree 10
              pctused 40
              initrans 1
              maxtrans 255
              storage
              (
              initial 20K
              next 20K
              minextents 1
              maxextents 249
              pctincrease 50
              );
              -- Create/Recreate primary, unique and foreign key constraints
              alter table SELECTOR
              add constraint PK_SELECTOR primary key (SELECTOR_ID)
              using index
              tablespace ORA_DATA
              pctfree 10
              initrans 2
              maxtrans 255
              storage
              (
              initial 20K
              next 20K
              minextents 1
              maxextents 100
              pctincrease 0
              );

              Heres the records:
              SELECTOR_ID     DESCRIPTION     LAST_COUNT     LAST_COUNT_DATE     LAST_COUNT_DURATION     SELECTOR_PARENT_ID
              1     Tester                    
              2     Morten     0     15.03.2004 18:46:24     207     1
              3     Uttrekksavvik     900     15.03.2004 19:33:35     19     1


              Hope this looks good when posted ;)

              Morten
              • 4. Re: Upgrade ODP.NET, strange problem
                373681
                The thing that makes the code break is:
                procedure s_selection(t_selection out sys_refcursor) is
                begin
                open t_selection for
                select s.last_count_duration
                from selector s;
                end;

                So there is something with that column, in row 3,
                The number is 19

                Any other columns are fine.

                Stupid really.

                analyze table validate structure gives no errors.
                • 5. Re: Upgrade ODP.NET, strange problem
                  373681
                  Interesting fact is that if I do
                  trunc(s.last_count_duration)
                  it works, fine. I guess there is some hidden info in the number. I have a bad habit of not putting restriction on those columns.

                  So as I can it is partly my fault, the code was fine with old driver.

                  Heres the stupid code that updates the number:
                  (sysdate - v_start) * 24 * 60 * 60
                  • 6. Re: Upgrade ODP.NET, strange problem
                    340203
                    When DataAdapter.Fill() is called, it tried to access the Number column using the .NET decimal value. However, the number column can hold numeric values which a .NET decimal cannot be supported. When this happens. an OverFlow exception will occur.

                    Thanks
                    Martha
                    • 7. Re: Upgrade ODP.NET, strange problem
                      87139
                      When DataAdapter.Fill() is called, it tried to access
                      the Number column using the .NET decimal value.
                      However, the number column can hold numeric values
                      which a .NET decimal cannot be supported. When this
                      happens. an OverFlow exception will occur.
                      Which is quite unfortunate because any query of the form
                      SELECT a/b from T
                      or
                      SELECT AVG(a) from T

                      Will sometimes generate an OverFlow exception, depending on the data. This is bad because you're likely to miss the problem in development and testing.

                      There was a thread last year about changing this behavior, and it shold really happen. At least there should be a config setting on the OracleConnection to silently round OracleDecimals when converting to .NET Decimals.

                      David

                      • 8. ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                        449052
                        This is a bug in the ODP.NET provider for oracle.

                        Consider the HR example database. if you fire the following query:
                        SELECT Trunc(Avg(Salary), 28) AS Result FROM HR.EMPLOYEES

                        you'll get the result: (could be different, my HR db is probably different)
                        6461.682242990654205607476635514

                        this is perfectly storable in a .NET decimal
                        Decimal d = 6461.682242990654205607476635514M;
                        compiles fine and work without a problem.

                        However when I call the query above with .NET code to fill a datatable I get the exception:
                        Method Enter: CreatePagingSelectDQ
                        Method Enter: CreateSelectDQ
                        Method Enter: CreateSelectDQ
                        Generated Sql query:
                             Query: SELECT TRUNC(AVG("HR"."EMPLOYEES"."SALARY"), 28) AS "Result" FROM "HR"."EMPLOYEES"
                        Method Exit: CreateSelectDQ
                        Method Exit: CreatePagingSelectDQ: no paging.
                        TestCase 'Unittests.TestLibrary.OracleTests.Adapter.OracleHRTests.DecimalOverflowTest' failed: System.OverflowException : Arithmetic operation resulted in an overflow.
                             at Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx)
                             at Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
                             at Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)
                             at Oracle.DataAccess.Client.OracleDataReader.GetValues(Object[] values)
                             at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges)
                             at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
                             at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
                             at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDataReader dataReader)
                             at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable dataTable, IDataReader dataReader)
                             at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
                             at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
                        ...

                        Now, is there ANYTHING which can be done to solve this, in SQL for example? as you can see, I already pass a TRUNC(), but that doesn't work always, for example if the real part of the decimal is a given size.

                        This is a real problem, and inside Oracle's code. When I peek into the GetDecimal code I see a lot of tests on bytes to filter out an overflow which to me are not necessary, as the Decimal struct of .NET will throw an overflow exception IF that's the case.

                        Frans Bouma
                        C# MVP
                        • 9. Re: ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                          24208
                          I'm not sure this is a bug in ODP -- you are doing:
                          SELECT Trunc(Avg(Salary), 28) AS Result FROM HR.EMPLOYEES
                          The trunc function, as specified here, will truncate the result to 28 decimal digits. However, the total number of digits in this results is 32 (on my system anyway). I believe the .NET decimal type can only hold up to 28 digits total.

                          That's my initial take anyway.

                          This code, which limits the total digits to 28 works correctly on my system:
                          using System;
                          using System.Data;
                          using Oracle.DataAccess.Client;
                          using Oracle.DataAccess.Types;
                          
                          namespace DataTableTest
                          {
                            /// <summary>
                            /// Summary description for Class1.
                            /// </summary>
                            class Class1
                            {
                              /// <summary>
                              /// The main entry point for the application.
                              /// </summary>
                              [STAThread]
                              static void Main(string[] args)
                              {
                                OracleConnection con = new OracleConnection("User Id=hr;Password=hr;Data Source=lt10gr2;Pooling=false");
                                con.Open();
                          
                                OracleCommand cmd = new OracleCommand("select trunc(avg(salary), 24) as result from hr.employees", con);
                                OracleDataAdapter da = new OracleDataAdapter(cmd);
                          
                                DataTable dt = new DataTable();
                                da.Fill(dt);
                          
                                da.Dispose();
                                dt.Dispose();
                                cmd.Dispose();
                                con.Dispose();
                              }
                            }
                          }
                          Corrections welcome, etc...

                          - Mark
                          • 10. Re: ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                            449052
                            It's a bug because as I stated: the very number returned by oracle itself can perfectly stored in a decimal:
                            Decimal d = 6461.682242990654205607476635514M;
                            compiles fine and runs fine at runtime, no overflow. Which means: ODP.NET throws an overflow exception while that's completely unnecessary. (the overflow exception is thrown by ODP.NET code btw, not by the Decimal struct, which is also weird, because the Decimal struct will check the value also)

                            My TRUNC statement was added to temp fix this problem, however normally this shouldn't be necessary, and I should be able to do:

                            SELECT Avg(Salary) AS Result FROM HR.EMPLOYEES

                            and load it into a datatable

                            Your '24' trunc will work with THIS example, but what if I have a set of values which also causes an overflow with trunc(.., 24) ?

                            The main issue is: Oracle code does some testing, thinks it's an overflow and instead of truncing in .NET code to 28 positions, it simply throws an exception, while the value itself is VALID. :)

                            Hardcode trunc values in queries is very unstable coding: if the values change, the code might crash in the future, which is always at runtime and very unfortunate.

                            Oracle's ODP.NET is the only provider which has this issue, all other .NET providers I work with don't do this, they simply trunc in-memory before GetValue returns the decimal, which is appropriate. IF the developer wants the FULL value, s/he has to call the OracleDataReader method to get the OracleDecimal value.

                            Which also could be an option: return the value in an OracleDecimal object instead of a Decimal. Either way: as long as it doesn;'t throw an exception (Which is even not legit!) because it's of absolutely no use, you can't work around it, you can't add a different codepath... besides writing your own datatable fill routine.
                            • 11. Re: ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                              24208
                              The main issue is: Oracle code does some testing, thinks it's an overflow and instead of truncing in .NET code to 28 positions, it simply throws an exception, while the value itself is VALID. :)
                              OK, I see what you are saying now.

                              Also, the Oracle documentation sort of leads you to believe that the in-memory, silent truncation will occur. It has this to say about the OracleDecimal structure:
                              When accessing the OracleDecimal.Value property from an
                              OracleDecimal that has a value greater than 28 precision,
                              loss of precision can occur.
                              You don't get a loss of precision, you get an exception.
                              Decimal d = 6461.682242990654205607476635514M;
                              compiles fine and runs fine at runtime, no overflow.
                              But doesn't that result in a silent truncation? On my system, the "14" at the end gets dropped. Yes, it runs with no errors, but dropping digits might not be desirable. I guess I don't 100% agree that because you can pass a value that is greater than the max number of digits, and no exception is thrown because the value is silently truncated means that is a "valid" value.
                              Oracle's ODP.NET is the only provider which has this issue, all other .NET providers I work with don't do this
                              I just ran your sample code without the "trunc" using the MS provider in .NET 2.0 and got:
                              OCI-22053: overflow error
                              in an exception.

                              - Mark
                              • 12. Re: ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                                449052
                                Also, the Oracle documentation sort of leads you to believe that the in-memory, silent truncation will occur
                                Exactly :) It was my understanding this would occur. It was my understanding it was similar to SqlServer's: If you want the large decimal value, call SqlDataReader.GetSqlDecimal, otherwise you get the truncated one from GetValue (which calls GetDecimal).

                                About the silent truncation: you might be right, I haven't checked that, I just checked for an overflow.
                                I just ran your sample code without the "trunc" using the MS provider in .NET 2.0 and got:(exception)
                                I meant: providers for other databases, like DB2, sqlserver, mysql, firebird etc. :)

                                An exception is the least someone needs. I can live with a truncated decimal. In fact, a truncated decimal is better than an exception, because there is no way to recover from that in the situation of loading data into a datatable.

                                Message was edited by:
                                FransBouma
                                • 13. Re: ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                                  24208
                                  I forwarded a link to this thread to a contact at Oracle. We'll see what comes about...

                                  - Mark
                                  • 14. Re: ODP.NET GetValue crash on legit decimal value. PLEASE FIX!
                                    24208
                                    Follow-up I received was:

                                    The exception being thrown is the intended and desired behavior. The documentation will be reviewed and updated as required.

                                    Now, while this might be something of a kludge, you could do this (assuming you really need the value as a number):
                                    select to_number(substr(to_char(avg(salary)),1,28)) from hr.employees
                                    to prevent the overflow exception. Note that this simply drops any digits beyond 28 - no rounding.

                                    - Mark
                                    1 2 Previous Next