14

I am working in c# .net 2 (Visual Studio 2005 SP1) attempting to fill a dataset with the results from a select * from table from an Oracle10g database. The .net framework, IDE and database cannot be changed at this client site.

I'm connecting using the ODP.net provider the dll version is 2.102.2.20

When I run the fill command I get an Exception:

Arithmetic operation resulted in an overflow

Also if I attempt to view the offending column in the Visual Studio designer (Show Table Data) I get for every row for this column in the table. The code works perfectly if my query selects other columns with integers for example omitting this column.

The column in question looks fine when I view it in the database from Toad, data looks like:

919.742866695572

I need the precision as it's required for a monte carlo simulation.

If instead of using a data adapter to fill the datatable I use a datareader and call dataReader.getValue(columnIndex) I get the same error but if I call dataReader.GetOracleDecimal(columnIndex) then I get the result I am looking for, no error.

I would rather use data adapter and filling a dataset (note these are untyped datasets as I couldn't get autogenerated strongly typed datasets to work from an oracle db). I don't want to use datareader and walk through the results (pick out the column values) as I am trying to write this as a generic method to work for many scenarios regardless of number of columns, index of decimal columns that would require specific get calls by datatype.

Can anyone help? Can I use new versions of the ODP.net dlls to connect to the older Oracle10g database? am wondering if this will help.

Thanks

m3ntat
  • 3,635
  • 11
  • 38
  • 50
  • Changing the ODP.net version to 11g (Oracle.DataAccess.dll 2.111.6.20) didn't help I still get the Arithmetic operation resulted in an overflow exception. One solution although not ideal is for to do this: SELECT TO_CHAR(offendingColumn) FROM Table But now my dataColumn types are different, I write this data out to Excel so I'm unsure if this will affect the cell formatting as well. – m3ntat Jun 25 '09 at 15:20
  • Also it means I need to know about each column in the Table I can't just do a blanket Select * From Table – m3ntat Jun 25 '09 at 15:21

3 Answers3

1

The problem is that the precision of the result value is too high to convert to a System.Decimal without some data loss. I forget the exact number of digits allowed, but it is around 18 or so. Is it acceptable to round() the result value to that many digits? In the example you gave, a round(MyColumn, 15) or so should be sufficient...

Chris R. Donnelly
  • 3,086
  • 3
  • 28
  • 28
0

You can try the latest version of ODP.Net (11g). It is backwards compatible. I use it to connect to a 10g database just fine. I think it should work with VS 2005 too. For clickonce deployment, just add the dlls referenced by this question: What is the minimum client footprint required to connect C# to an Oracle database? An important note is that if you have the latest version of odp.net, all of the dlls are included in the install directory. You don't need to download instant client separately. Just search for them.

Community
  • 1
  • 1
jle
  • 9,316
  • 5
  • 48
  • 67
  • Great am trying this on my computer will let you know how I get on. On the deployed machine do I need to install this as well? or I can just deploy my console app with newer version of Oracle.DataAccess.Dll ? it not I'd prefer to just deploy this app with all require dlls for oracle in my apps bin folder and not installation on the target deployed machine (a server), what is involved in this and which files are require? Thanks – m3ntat Jun 25 '09 at 14:46
  • You will want to Oracle Developer Tools for Visual Studio includes all of the dlls that you need. The only way to get it installed on client machine is either by installing full blown Oracle Client or using instant client (which I recommend). For instant client, you just need to find the .dlls (see http://stackoverflow.com/questions/70602/what-is-the-minimum-client-footprint-required-to-connect-c-to-an-oracle-database) for more information and add them to copy as content. This will ensure that they deploy properly. – jle Jun 25 '09 at 20:56
0

If you move to a higher version of ODP.NET like 12.x, the error just changes to "Invalid Cast Exception" from "Arithmetic overflow". You would have to CAST numeric values to cut-down insane precision values like 29 or 30 decimal places to make it more practical like 2 to 4 decimals.

To identify the columns and rows which have excessively big decimal values, you can run the SQL below after substituting the MY_SCHEMA, MY_TABLE and the number 10 with say 25 to identify columns that have values over 25 decimal places. This SQL will generate a SQL that should be run to identify the problem columns.

SELECT 'SELECT ' || LISTAGG('MAX(LENGTH(TO_CHAR(ABS(' || column_name || ') - FLOOR(ABS(' || column_name || '))))) - 1  AS decimals_' || column_name || CHR(13)
                    , CHR(9)|| ', ') WITHIN GROUP (ORDER BY rn)  ||
                    ' FROM ' || owner || '.' || table_name  || CHR(13) ||
                    ' WHERE '  || CHR(13) ||
                      LISTAGG('(LENGTH(TO_CHAR(ABS(' || column_name || ') - FLOOR(ABS(' || column_name || ')))) - 1) > 10 ' || CHR(13)
                                , CHR(9)|| '  OR  ')
WITHIN GROUP (ORDER BY rn) AS Nasty_Numbers_Finder_Query
FROM
(
    SELECT  owner, table_name, column_name,
        row_number() OVER ( PARTITION BY table_name  ORDER BY rownum) rn
    FROM  dba_tab_columns
    WHERE
        OWNER = 'MY_SCHEMA'
        AND table_name = 'MY_TABLE'
        AND (data_type LIKE '%FLOAT%'
            OR data_type LIKE '%NUMERIC%')
) a
GROUP BY owner, table_name

For more information, I have blogged about it here.