hi,
I am seeing this really weird behavior on Oracle 12.2.0.1.0 and Oracle Version 19.18.0.0.0:
When I update the geometry column for a single row using a pl/sql function, the code is executed 8 (EIGHT!) times, whereas a numeric numeric column updated with another function gets updated only once (as expected).
I added a DBMS_LOCK.SLEEP(1)/DBMS_SESSION.SLEEP(1) inside the PL/function updating the geometry column and it takes 8 seconds to be executed.
The other strange thing is that the update on the numeric column is performed sometime in between the first and the eighth update on the geometry function (I saw that by logging the operation with an autonomous transaction).
You can try yourself with the script I provided, you should be able to run it from start to finish and see the results, the sequence used by the numeric function will return 1 whereas the other sequence will return 8 !
Here is output taken from my database:
============================================================================
Sequence DUMMY_SEQ created.
Sequence DUMMY2_SEQ created.
NEXTVAL
----------
0
NEXTVAL
----------
0
Table "TEST_GEOM" created.
1 row inserted.
Commit complete.
Function DUMMY2 compiled
Function DUMMY compiled
1 row updated.
Elapsed: 00:00:08.018
Commit complete.
Elapsed: 00:00:00.000
CURRVAL
----------
8
CURRVAL
----------
1
Sequence DUMMY_SEQ dropped.
Sequence DUMMY2_SEQ dropped.
Table "TEST_GEOM" dropped.
============================================================================
Any ideas?!?
Thank you
Flavio
CREATE SEQUENCE DUMMY_SEQ MINVALUE 0 ORDER;
CREATE SEQUENCE DUMMY2_SEQ MINVALUE 0 ORDER;
SELECT DUMMY_SEQ.NEXTVAL FROM DUAL;
SELECT DUMMY2_SEQ.NEXTVAL FROM DUAL;
CREATE TABLE "TEST_GEOM"
( "ID" INTEGER,
"NUM" NUMBER,
"SHAPE" SDO_GEOMETRY
);
SET DEFINE OFF;
Insert into TEST_GEOM (ID, SHAPE)
values (1, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1, 53, 2003, 1), MDSYS.SDO_ORDINATE_ARRAY(1617857.39992003, 4927454.61993996, 1617859.95997274, 4927451.20990379, 1617869.76990222, 4927448.42992377, 1617871.36989997, 4927394.54999967, 1617870.37998793, 4927351.50990073, 1617883.78998438, 4927349.87990116, 1617900.64988697, 4927346.58992017, 1617900.31990137, 4927344.38001657, 1617893.83995069, 4927309.679913, 1617915.1700947, 4927297.58996787, 1617928.12005408, 4927335.75984816, 1617940.09995791, 4927328.25990929, 1617945.94009625, 4927325.55002712, 1617943.32575782, 4927334.82798004, 1617943.28014197, 4927334.99066672, 1617944.51009718, 4927354.62002357, 1617936.85009823, 4927386.20001927, 1617932.40883821, 4927415.70410291, 1617932.61397489, 4927415.84461214, 1617938.54007733, 4927412.46993659, 1617944.46009097, 4927419.68995848, 1617951.05009863, 4927434.1299835, 1617957.74998072, 4927474.19009812, 1617887.96998141, 4927460.48009826, 1617866.40997905, 4927456.55009778, 1617857.39992003, 4927454.61993996, 1617887.6001005, 4927375.78008369, 1617887.72009935, 4927395.83989044, 1617905.43989081, 4927394.20990962, 1617903.38991087, 4927372.97011743, 1617887.6001005, 4927375.78008369)));
COMMIT;
CREATE OR REPLACE FUNCTION DUMMY2(
P_NUM IN NUMBER
)
RETURN NUMBER
AS
I INTEGER;
BEGIN
I := DUMMY2_SEQ.NEXTVAL;
RETURN P_NUM;
END;
/
CREATE OR REPLACE FUNCTION DUMMY(
P_SHAPE IN SDO_GEOMETRY
)
RETURN SDO_GEOMETRY
AS
I INTEGER;
BEGIN
DBMS_SESSION.SLEEP(1);
I := DUMMY_SEQ.NEXTVAL;
RETURN P_SHAPE;
END;
/
SET TIMING ON
UPDATE TEST_GEOM
SET SHAPE = DUMMY(SHAPE),
NUM = DUMMY2(10)
WHERE ID = 1;
COMMIT;
SET TIMING OFF
SELECT DUMMY_SEQ.CURRVAL FROM DUAL;
SELECT DUMMY2_SEQ.CURRVAL FROM DUAL;
DROP SEQUENCE DUMMY_SEQ;
DROP SEQUENCE DUMMY2_SEQ;
DROP TABLE TEST_GEOM PURGE;