I need to insert values with a precision of 5 decimal places into an Oracle interface table via OPENQUERY because the values are originally stored in an SQL database. The data type of the Oracle table column is NUMBER (with no scale/precision specified). Using OPENQUERY to insert a value of 1.4, results in a value of 1.3999999999999999 stored in the Oracle table. I cannot change the data type of the Oracle table to NUMBER(38,5) because it is a standard Oracle table (GL_DAILY_RATES_INTERFACE).
According to Oracle https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
"If a precision is not specified, the column stores values as given."
Which means that if I insert 1.4, it should be stored in a NUMBER column as is. But it doesn't. So does that mean that when inserting through OPENQUERY to a linked Oracle server, the Oracle Provider for OLE DB does some addition conversion that results in a floating point error?
How do I insert values to a precision of 5 decimal places into an Oracle table NUMBER column that does not have precision or scale specified?
Update: My insert statement does round the values when inserting. But it doesn't solve the issue. For example:
INSERT INTO OPENQUERY(LINKEDSERVER, "SELECT CONVERSION_RATE FROM GL_DAILY_RATES_INTERFACE") VALUES(ROUND(1.4,5))
Since inserting values through OPENQUERY to a linked Oracle server causes some floating point error, I tried using EXEC('') AT LINKEDSERVER and it worked. Because the statement is executed directly on the ORACLE server, there is no longer any issue of the Oracle Provider for OLE DB doing any unexpected conversion.
My overall solution was to first insert values from the SQL table to the Oracle table using OPENQUERY, then use EXEC() to update and round the values in the Oracle table again.