Losing decimals when using Oracle SDO_POINT in a view

Question

We have a table containing coordinates, among other things. We used to store these in two number fields (x and y), but we've now replaced this with an SDO_GEOMETRY field. For backwards compatibility, we have created a view (with the same name as the table used to have), where we do:

create or replace view meas_pnt as select ..., m.position.SDO_POINT.X x_coordinate, m.position.SDO_POINT.Y y_coordinate from meas_pnt_tab m;

This works for both reading and writing, but when writing decimal numbers to the view, the decimals are lost. I can't figure out why. Can anybody help? The following illustrates the problem:

update meas_pnt_tab m set m.position.SDO_POINT.x = 2.3 where meas_key=1; select m.position.SDO_POINT.X from meas_pnt_tab m where meas_key=1; -> x is 2.3. Good!

update meas_pnt set x_coordinate = 2.3 where meas_key=1; select m.position.SDO_POINT.X from meas_pnt_tab m where meas_key=1; -> x is 2.

Answer 1

Hi Tor,

I can not reproduce your error. This is what I get:

SQL> CREATE TABLE meas_pnt_tab (position sdo_point_type, meas_key NUMBER);

Table created
SQL> INSERT INTO meas_pnt_tab (position, meas_key) 
  2  VALUES (sdo_point_type(2.3,0,0), 1);

1 row inserted
SQL> SELECT m.position.X FROM meas_pnt_tab m WHERE meas_key=1;

POSITION.X
----------
       2,3
SQL> CREATE OR REPLACE VIEW meas_pnt AS
  2  SELECT m.position.X x_coordinate,
  3         m.position.Y y_coordinate,
  4         m.meas_key
  5    FROM meas_pnt_tab m;

View created
SQL> UPDATE meas_pnt SET x_coordinate = 2.4 WHERE meas_key=1;

1 row updated
SQL> SELECT m.position.X FROM meas_pnt_tab m WHERE meas_key=1;

POSITION.X
----------
       2,4

Can you post your CREATE statements ?