Function Called 8 Times Updating A SDO_Geometry Column (Doc ID 1616448.1)

Last updated on FEBRUARY 21, 2014

Applies to:

Oracle Spatial and Graph - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

When updating following sdo_geometry column based on the results of a pl/sql function, the function is called 8 times for just one row.
This does not happen with a select or if a simple datatype is updated with a function instead.

SQL> CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);  2    3    4

Table created.

SQL> INSERT INTO cola_markets VALUES(
2   1,
3   'cola_a',
4   SDO_GEOMETRY(
5     2003,  -- two-dimensional polygon
6     NULL,
7     NULL,
8     SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
9     SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
10           -- define rectangle (lower left and upper right) with
11           -- Cartesian-coordinate data
12   )
13  );

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function sdo_upd(sdo_in mdsys.sdo_geometry)
return mdsys.sdo_geometry
as
begin
dbms_output.put_line('sdo_upd');
return sdo_in;
end;
/  2    3    4    5    6    7    8

Function created.

SQL> set serveroutput on
SQL> -- function run 1 time
SQL> select sdo_upd(shape) from cola_markets where mkt_id=1;

SDO_UPD(SHAPE)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDIN
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(1, 1, 5, 7))


sdo_upd
SQL> -- function run 8 times
SQL> update cola_markets set shape = sdo_upd(shape) where mkt_id=1;
sdo_upd
sdo_upd
sdo_upd
sdo_upd
sdo_upd
sdo_upd
sdo_upd
sdo_upd

1 row updated.

SQL> -- function run 1 time
SQL> update cola_markets set shape =
(select sdo_upd(shape) from cola_markets where mkt_id=1) where mkt_id=1;  2
sdo_upd

1 row updated.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms