Problem : Decimal's precision value is out of range

Stefan · Post by **Stefan** » Wed 26 Mar 2008 11:03

I open an oracle database table with the OracleDataTable Class of
then OraDirect-Library. After calling the .Open() Method, I get an Error-Message like "Decimal's precision value is out of range".

The Datatype of the columns in Oracle is defined as Number(), without
any definition of scale or precision.

My code :

OracleDataTable oraQry = new OracleDataTable(" ", MyOracleConn );

oraQry.Close();
oraQry.Clear();
oraQry.SelectCommand.CommandText = "SELECT * FROM MyTable "
+ " WHERE PK_COL = 1231231231 "
+ " ORDER BY SORT_COL";

oraQry.FetchAll = true;
oraQry.Open();

--> After this, I get the following Exception

... Decimal's precision value is out of range
... ... bei CoreLab.Common.DbDataTable.a(Int32 A_0, Boolean A_1)
... ... bei CoreLab.Common.DbDataTable.b(Boolean A_0)
... ... bei CoreLab.Common.DbDataTable.m()
... ... bei CoreLab.Common.DbDataTable.c()
... ... bei CoreLab.Common.DbDataTable.Open()

I am using the corelab version 4.20

oraQry implements the columns after opening the Table as System.Decimal. I think I need a type like System.Double. But I am not able to change the generated Types.

What can I do ?

Alexey.mdr · Post by **Alexey.mdr** » Wed 26 Mar 2008 11:18

The Datatype of the columns in Oracle is defined as Number(), without
any definition of scale or precision.

On the client side you need to use OracleNumber data type.
Probably the following method will be suitable:
public OracleNumber OracleDataReader.GetOracleNumber(int i)

Stefan · Post by **Stefan** » Wed 26 Mar 2008 12:11

Thanks.

But my finished algorithm to analyze the Table-Contents needs the hole table-data in the Table.Rows property. It would cost a lot of time to change the code.

Is there no other solution as using the DataReader ?

Alexey.mdr · Post by **Alexey.mdr** » Wed 26 Mar 2008 13:03

Try this code:

Code: Select all

OracleDataTable oraQry = new OracleDataTable(" ", MyOracleConn );

oraQry.Close();
oraQry.Clear();
oraQry.SelectCommand.CommandText = "SELECT * FROM MyTable "
+ " WHERE PK_COL = 1231231231 "
+ " ORDER BY SORT_COL";
oraQry.ReturnProviderSpecificTypes = true;
oraQry.FetchAll = true;
oraQry.Open();

Setting oraQry.ReturnProviderSpecificTypes to true should solve the problem.

Stefan · Post by **Stefan** » Wed 26 Mar 2008 14:27

I tried it.

But without an installed oracle client, the function is not present.

I searched trough my Table ( 1000 Columns and 2500 Rows ) by using your DataReader solution. There I found a value of 2.00232E33. That is
to much for the decimal datatype. Your GetOracleNumber(int i).Value returns a decimal. What can I do to read it as a double ??

Alexey.mdr · Post by **Alexey.mdr** » Thu 27 Mar 2008 12:16

Did this help?

Code: Select all

oraQry.ReturnProviderSpecificTypes = true;

What can I do to read it as a double ??

You need to convert the value, but beware of the possible data loss.

Stefan · Post by **Stefan** » Sun 30 Mar 2008 18:03

So, I solved the problem by using the DataReader and your GetOracleNumber function.

Large values are filtered out by Excpetion-Handling. That means, I parse every row and column and try to convert the database-value to a decimal ( GetOracleNumber(i).Value ).

Whenever the convertion fails, I ignore the data for my analysis through setting a default value in then exception block.

Thanks for your help.

But for future versions, it would be fine when you support access to double values in then database.

Alexey.mdr · Post by **Alexey.mdr** » Mon 31 Mar 2008 12:47

Could you enlist the disadvantages (for you) of the OracleNumber type?
Where exactly do you want to get doubles?
Probably you can use this method:
OracleNumber.ToDouble(GetOracleNumber(i)) ?