"Oracle Number Data Type Input String was not in the Correct Format" error appears
kbt136506
DOWNLOADSIssue
After upgrading from 4.6.11 to 4.7, workflows involving Oracle SmartObject(s) may show an error:
"Input String was not in the correct format"
Symptoms
This is related to changes to the Oracle service broker between 4.6.11 and 4.7 to better support the Oracle "Number" data type when "Precision" and/or "Scale" property is NOT explicitly specified in Oracle.
In 4.6.11, if a "Number" data type is used but the "Precision" and "Scale" property in Oracle was NOT specified, a stored Integer "1", will be returned a "1". This field will also return data up to the implicit setting of "Precision = 29" and "Scale = 9" by the Oracle service broker. This setting is not configurable in 4.6.11.
In 4.7, if an Integer "1" is stored in Oracle and the "Precision" and "Scale" is not specified, K2 will return it as "1.000000000". This can result in the "Input String was not in the Correct Format" error when interacting with this SmartObject field and the "Integer" Or "Decimal" Data field type within a workflow (if proper conversion inline functions is not used).
An enhancement is introduced in the 4.7 February CU that will add a "Decimal Precision and Scale - Required" Service Key to the Oracle Service Instance. That will allow setting the default "Precision" and "Scale" values if a "Number" data type is used in Oracle and the "Precision" and "Scale" is not explicitly specified in Oracle.
"When trying to create a SmartObject for a specific table using the Oracle service instance, the following error occurred "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column"."
Later CUs (May/August) will also have this change.
Troubleshooting Steps
Changes to the data types involved at different layers will likely need to be considered/reconsidered.
The data type flow may be as such:
Oracle "Number(Precision, Scale)" data type --> SmartObject Property data types (Decimal/Number) --> Workflow Data Field types (Decimal/Integer) and/or SmartForms data type (Decimal, Number)
It may be best to explicitly specify a Precision and Scale when creating an Oracle Number data type column; as this will allow you to control the data type between the different layers and perhaps not fall to the mercy of the "Decimal Precision and Scale - Required" service key, as this setting will be system wide for all Number columns in Oracle in which the Precision and Scale was not specified.
For example:
If you wish to store an Integer "1" in Oracle, perhaps the following scenario will keep data type consistent across the layers:
Oracle Number(29, 0) --> SmartObject Property (Number) --> Workflow Data Field (Integer)
*Explicitly specifying a Scale = 0 in Oracle, will ensure that only an Integer will be stored and returned
** A generated SmartObject will surface this field as "Decimal", but it can be edited and changed to "Number" if this field should only ever return an Integer
If you need to set a Scale = 0 for the "Decimal Precision and Scale - Required" service key to control all "Number" data type from Oracle that do not have the Precision and Scale specified, set the service key with only the Precision value such as "29". Specifying "29,0" for this service key will throw an error:
