Oracle looses float precision when using a trigger

Question

I use a trigger to fill a SDO_POINT_TYPE, or SDO_GEOMETRY column from two other columns of the same table.
For some reason, the trigger changes the floating values precision when compared with direct insertion.
I wonder if there is a simple way to avoid loosing precision.

DROP TABLE test;
CREATE TABLE test (LONGITUDE BINARY_DOUBLE, LATITUDE BINARY_DOUBLE, p SDO_point_type);

CREATE OR REPLACE TRIGGER TEST_TRIGGER 
BEFORE UPDATE OF LATITUDE, LONGITUDE ON TEST
FOR EACH ROW  
BEGIN
    :new.p := sdo_point_type(:new.LONGITUDE, :new.LATITUDE, NULL);
END;

INSERT INTO test(p) VALUES(NULL);
UPDATE test SET longitude=-14.19, latitude=48.33; -- use trigger

INSERT INTO test(p, longitude, latitude) VALUES(sdo_point_type(-14.19, 48.33, NULL), -14.19, 48.33); -- no trigger
SELECT CAST(latitude AS VARCHAR2(100)) AS lat, CAST(t.p.y AS VARCHAR2(100)) AS point_lat FROM test t;

-- LAT                      POINT_LAT
-- 4,8329999999999998E+001  48,329999999999998  -- with trigger
-- 4,8329999999999998E+001  48,33               -- without trigger

Answer 1

Stop using BINARY_DOUBLE and use the NUMBER data-type.

---

The difference is nothing to do with the trigger; it is to do with the data-types you are using and that BINARY_DOUBLE stores the closest approximation it can to a value whereas NUMBER stores an exact value.

SELECT CAST(48.33 AS NUMBER),
       CAST(48.33 AS BINARY_DOUBLE)
FROM   DUAL;

Outputs:

CAST(48.33ASNUMBER)	CAST(48.33ASBINARY_DOUBLE)
48.33	4.8329999999999998E+001

From the Oracle Data-Types documentation:

Code	Data Type	Description
2	NUMBER [ (p [, s]) ]	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.
101	BINARY_DOUBLE	64-bit floating point number. This data type requires 8 bytes.

Floating-Point Numbers

[...]

Binary floating-point numbers differ from NUMBER in the way the values are stored internally by Oracle Database. Values are stored using decimal precision for NUMBER. All literals that are within the range and precision supported by NUMBER are stored exactly as NUMBER. Literals are stored exactly because literals are expressed using decimal precision (the digits 0 through 9). Binary floating-point numbers are stored using binary precision (the digits 0 and 1). Such a storage scheme cannot represent all values using decimal precision exactly. Frequently, the error that occurs when converting a value from decimal to binary precision is undone when the value is converted back from binary to decimal precision. The literal 0.1 is such an example.

This means that:

• BINARY_DOUBLE stores the closest approximation possible of a number.
• NUMBER stores the exact number (provided the number is within the bounds specified by the precision and scale).

48.33 is not possible to store exactly using an IEEE 64-bit floating point number and the closest possible representation is 48.329999999999998. You can store it exactly using NUMBER data type.

So, if you want to store the value exactly then use an exact data-type.

CREATE TABLE test (LONGITUDE NUMBER, LATITUDE NUMBER, p SDO_point_type);

CREATE OR REPLACE TRIGGER TEST_TRIGGER 
BEFORE UPDATE OF LATITUDE, LONGITUDE ON TEST
FOR EACH ROW  
BEGIN
  :new.p := sdo_point_type(:new.LONGITUDE, :new.LATITUDE, NULL);
END;
/

INSERT INTO test(p) VALUES(NULL);
UPDATE test SET longitude=-14.19, latitude=48.33; -- use trigger
INSERT INTO test(p, longitude, latitude) VALUES(sdo_point_type(-14.19, 48.33, NULL), -14.19, 48.33); -- no trigger

SELECT CAST(latitude AS VARCHAR2(100)) AS lat,
       CAST(t.p.y AS VARCHAR2(100)) AS point_lat
FROM   test t;

Outputs:

LAT	POINT_LAT
48.33	48.33
48.33	48.33

---

If you really want to use BINARY_DOUBLE and want the same values in the SDO_POINT as the column then insert values into the point that have been CAST to BINARY_DOUBLE:

INSERT INTO test(
  p,
  longitude,
  latitude
) VALUES(
  sdo_point_type(CAST(-14.19 AS BINARY_DOUBLE), CAST(48.33 AS BINARY_DOUBLE), NULL),
  -14.19,
  48.33
);

or using BINARY_DOUBLE literals:

INSERT INTO test(
  p,
  longitude,
  latitude
) VALUES(
  sdo_point_type(-14.19D, 48.33D, NULL),
  -14.19D,
  48.33D
);

fiddle