OCI-22053: overflow error can occur when quering a decimal field on Oracle

Performing a query with a decimal field on Oracle using the fcSDK can throw an error:

OCI-22053: overflow error

One particular customer saw this error when viewing a ClearContracts contract in Dovetail Agent (fcClient).

The total_net column is defined as decimal (19,4), meaning that there should only be 4 digits after the decimal point. But, the data being stored was 7480.2199999999999999999999878

As an example of how to reproduce:
create a new column of decimal (19.4) and set its value to 7480.2199999999999999999999878 using sql plus.
Then query for this contract using the fcSDK, and you will get the overflow error.

In older versions of Clarify, when Clarify schema editiong tools (ddcomp, DD Editor) created a decimal column, it would create it at the Oracle level as a NUMBER with the same precision.
If you created a column of decimal(19,4) in Clarify, then it would get cerated at the Oracle level as NUMBER(19,4)

In later versions of Clarify, the column would get created at the Oracle level simply as NUMBER, without any precision. So, the database data could have more precision than the Clarify schema definition allowed for.

The overflow error is happening because the precision of the decimal in Oracle is greater than the precision capable in ADO.NET, which is used within the SDK.
More specifically, the issue actually lies in Microsoft's OracleClient library (System.Data.OracleClient).
Microsoft has decided not to fix this issue.
More details are here:
http://connect.microsoft.com/VisualStudio/feedback/details/94626/bug-with-oracleclient-overflow-error-oci-22053

Dovetail has provided a workaround for this issue within the Dovetail SDK, as of version 3.2.4

To workaround a bug in how the .Net Oracle client handles decimal values with high precision, we now truncate (via SQL) decimal fields to a precision matching what is specified in the Clarify schema.

An important note: This truncation will only occur if the DataFields are specified for the query object. If you do not specify specific columns to be queries for (essentially meaning you would do a "select * " query), then the truncation will NOT occur.

As an example, the following code:

var c = FCSession.CreateGeneric(‘contract');
c.DataFields = 'objid, total_gross;
c.AppendFilter('objid','=',100);
c.Query();

will produce the following SQL:

SELECT table_contract.objid, trunc(total_gross, 4) as total_gross FROM table_contract WHERE ( ( objid = :param0 ) )

Notice that the total_gross column is being truncated to 4 decimal places, which is the defined precision of that column in the Clarify schema.

An additional workaround is to alter the database column so that its precision and scale at the Oracle level matches its definition in the Clarify schema.

As an example, consider the column total_net on table_contract.
In Clarify 13 (Amdocs 6), this column is defined in the schema as decimal(19,4).
But at the Oracle level, its defined as simply NUMBER.

We can alter the database to make the column at the Oracle level be NUMBER (19,4).

Here's the steps on how to do this, using table_contract.total_net as an example

alter table table_contract add temp_number NUMBER;
update table_contract set temp_number = total_net;
update table_contract set total_net=null;
alter table table_contract modify total_net number(19,4);
update table_contract set total_net=trunc(temp_number,4);
alter table table_contract drop column temp_number;