Question
James Casazza · Aug 23, 2021

Data Values being Rounded to 2 Decimal places with Linked Oracle Table with NUMBER Data Types

After linking in Oracle Table with Field Column's Data Types of NUMBER, my updates into these table fields are resulting in data that is being rounded to 2 decimal places. I insert a record with 1234.1234 and 1234.12 is stored.

It appears Cache xDBC might be manipulating my values prior to sending to Oracle. Is there a setting or system parameter that is controlling this? If so, is there a way to relax this from occurring so the values I send are being stored in Oracle with the same values?

Product version: Caché 2017.1
$ZV: Cache for Windows (x86-64) 2017.1.3 (Build 317U) Tue Apr 10 2018 13:31:53 EDT
00
1 0 10 130
Log in or sign up to continue

Thanks Robert - I'm not using IRIS but Cache with JDBC. Also, with the %Library.Numeric parameter Scale, how and where do I implement it? Can a system wide setting be set for this control?

if you link a table from another DB  (Oracle) there is a related class generated in Caché.
In this class a property related to your column with type %Numeric.
all you have to do is to add (SCALE=4)  or whatever scale you need.
this data type existed already in Caché 5.*  about 20 years back. 

The system-wide control is stored in Cache.cpf file.
You can change ist from SMP in  System > Configuration > System-defined DDL Mappings 
though I would not recommend to do it on a system-wide scope
as this affects ALL mapping between Caché Objects and SQL

Hi Robert,

When I review the existing DDL Mapping for NUMBER, the datatype states: %Library.Numeric(SCALE=0)

This does not make any sense my generated SQL Table code(.cls & .int) is validating with EXTERNALSQLTYPE=2 and $isvalidnum(%val,2,,)

I thought SCALE=0 allowed any decimal value but it appears somewhere else there is logic that is defaulting this SCALE to 2.

And therefore you should edit the generated class and not touch any defaults.
I assume it's the Link Generator that uses SCALE=2 if Oracle doesn't provide anything.

I have a link generator written that is reading Source (Oracle) tables and that uses the %Library.ResultSet class. for each it gets column name, type, maxsize, precision, lais, readonly, required & data type name and builds Cache Linked Table.

Are you referring to this logic where I need to apply scale? I do not see any SCALE references in logic.

 if (jdbc) {
  s rs= ##class(%Library.ResultSet).%New("%GTWCatalog:SQLFieldsJ")
 } else {
  s rs= ##class(%Library.ResultSet).%New("%GTWCatalog:SQLFields")
 }
 s sc = rs.Execute(gateway, table, schema)
 if ($$$ISERR(sc)) {
  d $system.OBJ.DisplayError(sc)
  quit sc
 }
 s tname = schema _ "." _ table
 s cols = ""
 while rs.Next() {
  s externalcolumnname=rs."COLUMN_NAME",
    columnname = externalcolumnname,
    columntype=rs.DATATYPE,
    columnmaxsize=rs."COLUMN_SIZE",
    columnprecision=rs."DECIMAL_DIGITS",
    columnalias="" /* defaults to externalcolumnname */,
    readonly=0 /* hardcoded */,
    required='rs.NULLABLE,
    notused="",
   datatypename=rs."DATATYPE_NAME"
   if prikey="",(externalcolumnname["_cd")!(externalcolumnname["_id") SET prikey=externalcolumnname
  s col=$LTS($LB( externalcolumnname,
     columnname,
    columntype,
    columnmaxsize,
    columnprecision,
    columnalias,
    readonly,
    'required,
    notused,
    datatypename ),"^")
  s cols = cols_$LB(col)
 }
 s cols=$LTS(cols,"@")
 d rs.Close()
 

ah!

I have a link generator written that is reading Source (Oracle) tables and that
uses the %Library.ResultSet class. for each it gets column name, type, maxsize,
precision, lais, readonly, required & data type name and builds Cache Linked Table.

So you don't use the Caché provided Default Link generator in SMP !!!
So you get the default of datatype class %Numeric  (=> SCALE=2)

It might be enough to add :

$SELECT(columntype["Numeric":"%Numeric (SCALE=4)",1:columntype) 

It's all under your own control.

Why SCALE=4? Wouldn't that fix 4 decimal placement? I'm am looking for a SCALE that will allow any number with any decimal placement.

the maxSCALE = 18 
see Doc on $NORMALIZE()
used in ##class/%Library.Numeric).Normalize()

/// Converts <var>%val</var> to a normalized value.
ClassMethod Normalize(%val As %RawString) As %Numeric [ CodeMode = generator, ServerOnly = 1 ]
{
%codemode=$$$cMETHCODEMODEEXPRESSION
%code="$select($zu(115,13)&&(%val=$c(0)):"""",1:$normalize($decimal(%val),"_+%parameter("SCALE")_"))"
QUIT $$$OK
}

But it is no problem to clone this data tape and write a private one

Please share your table structure along with cache class structure