Decimal with Negative Scale

[Mark Biamonte <Mark.Biamonte@progress.com>]

I looked into the behavior of how our different products handle the Oracle Number type with a negative scale.  To do this I created a table in Oracle with the following columns

 

Column Name	Definition
Id	Int
Num53	Number (5, -3)
Num105	Number (10, -5)
NumMax	Number (38, -3)

 

 

DataDirect Cloud OData API

 

Our DataDirect Cloud product allows a user to overlay an OData service on top of an Oracle Database.   The OData Service describes the Numxxx columns as an Edm.Decimal with the Precision specified as precision + abs(scale) and the scale is specified as zero. 

 

<EntityType Name="FOONEGSCALE">

<Key><PropertyRef Name="ID" /></Key>

<Property Name="ID" Type="Edm.Decimal" Nullable="true" Precision="38" Scale="0" />

<Property Name="NUM53" Type="Edm.Decimal" Nullable="true" Precision="8" Scale="0" />

<Property Name="NUM105" Type="Edm.Decimal" Nullable="true" Precision="15" Scale="0" />

<Property Name="NUMMAX" Type="Edm.Decimal" Nullable="true" Precision="41" Scale="0" />

</EntityType>  

 

DataDirect JDBC driver for Oracle

 

The JDBC specification allows for the scale value to be less than zero.  Our JDBC driver for Oracle reports the precision and scale as how it is defined in the database when describing columns in a table and columns in a result set.  For example when describing the table, the Num105 column is described with a precision of 10 and a scale of -5.  When fetching the data from the result set the java BigDecimal value that is returned has a precision equal to precision + abs(scale) and a scale of zero.  For example the BigDecimal value returned for the Num105 column reports a precision of 15 and a scale of 0.

 

Oracle’s JDBC driver

Oracle’s JDBC driver behaves the same as our JDBC driver.  It describes the columns with the precision and scale stored in the database.  The Big Decimal returned when fetching a column value is precision + abs(scale) and the scale is zero.

 

DataDirect ODBC driver for Oracle

 

Like the Data Direct and Oracle JDBC drivers, our ODBC driver for Oracle describes the columns with the precision and scale in the database.  In ODBC if you fetch an Oracle number value as a NUMERIC or DECIMAL type, the value comes back in a structure that is limited to 16 digits of precision.  This structure is not large enough to hold the full range of values that the Oracle Number type can hold.  In particular, the values for the NumMax column cannot be fetched as a NUMERIC or DECIMAL in ODBC because the precision of the values in that column is too large.  To fetch the NumMax column values, they must be fetched as a string

 

Mark