SQL> CREATE OR REPLACE PROCEDURE SP_SHIFT_POINT_COORD(X_OLD FLOAT,Y_OLD FLOAT,X_NEW FLOAT,Y_NEW FLOAT,V_EXCH VARCHAR2)
  2  AS
  3     v_geom          MDSYS.SDO_GEOMETRY ;
  4     ann_x           FLOAT ;
  5     ann_y           FLOAT ;
  6     x_offset        FLOAT;
  7     y_offset        FLOAT;
  8     cur_2           SYS_REFCURSOR;
  9     v_sqlstr        VARCHAR2(1000);
 10     cur_3           SYS_REFCURSOR;
 11
 12
 13  BEGIN
 14
 15     IF((X_OLD>X_NEW) AND (Y_OLD>Y_NEW))THEN
 16             x_offset :=(X_OLD-X_NEW);
 17             y_offset :=(Y_OLD-Y_NEW);
 18
 19     ELSIF((X_OLD<X_NEW) AND (Y_OLD<Y_NEW))THEN
 20             x_offset :=(X_NEW-X_OLD);
 21             y_offset :=(Y_NEW-Y_OLD);
 22     END IF;
 23
 24  FOR cur_1 IN ( SELECT table_name FROM lkp WHERE (feature_geom_type = 'POINT' OR feature_geom_type='TEXT') AND FEATURE_CLASS IS
NOT NULL AND TABLE_NAME='M')
 25  LOOP
 26     --dbms_output.put_line(1);
 27     OPEN cur_2 FOR 'SELECT  a.GEOM from '||cur_1.table_name||' a WHERE a.EXCHANGE_CODE='||''''||V_EXCH||''''||'';
 28
 29             LOOP
 30             --dbms_output.put_line(2);
 31
 32                     FETCH cur_2 INTO  v_geom;
 33                     EXIT WHEN cur_2%NOTFOUND;
 34
 35                     ann_x := v_geom.sdo_point.x + x_offset ;
 36                     ann_y := v_geom.sdo_point.y + y_offset ;
 37
 38                     v_geom := MDSYS.SDO_GEOMETRY
 39                     (2001,
 40                     NULL,
 41                     MDSYS.SDO_POINT_TYPE(ann_x,ann_y,NULL),
 42                     NULL,
 43                     NULL
 44                     );
 45
 46                     v_sqlstr:= 'update '||cur_1.table_name||' A set A.GEOM = :1';
 47                     EXECUTE IMMEDIATE v_sqlstr USING v_geom;
 48
 49             END LOOP;
 50
 51
 52     OPEN cur_3 FOR 'SELECT  a.TEXT_LOCATION from '||cur_1.table_name||'_ann a WHERE '||cur_1.table_name||'_ref in (select '
 53     ||cur_1.table_name||'_id from '||cur_1.table_name||' where exchange_code = '||''''||V_EXCH||''''||')';
 54             LOOP
 55                     --dbms_output.put_line(3);
 56                     FETCH cur_3 INTO v_geom;
 57                     EXIT WHEN cur_3%NOTFOUND;
 58                     --dbms_output.put_line(4);
 59                     ann_x := v_geom.sdo_point.x + x_offset ;
 60                     ann_y := v_geom.sdo_point.y + y_offset ;
 61                     --dbms_output.put_line(5);
 62                     v_geom := MDSYS.SDO_GEOMETRY
 63                     (2001,
 64                     NULL,
 65                     MDSYS.SDO_POINT_TYPE(ann_x,ann_y,NULL),
 66                     NULL,
 67                     NULL
 68                     );
 69                     --dbms_output.put_line(6);
 70                     v_sqlstr:= 'update '||cur_1.table_name||'_ann A set A.TEXT_LOCATION = :1';
 71                     EXECUTE IMMEDIATE v_sqlstr USING v_geom;
 72                     --dbms_output.put_line(7);
 73
 74             END LOOP;
 75  END LOOP;
 76
 77  commit;
 78  EXCEPTION WHEN OTHERS THEN
 79     RAISE_APPLICATION_ERROR(-20010,SQLERRM);
 80  END SP_SHIFT_POINT_COORD;
 81  /

Procedure created.

SQL> exec SP_SHIFT_POINT_COORD(2886054.105351,538100.958364,2261392.566000,210137.926100,'11');

PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON SIZE 900000;

--create needed types and table types
CREATE OR REPLACE TYPE fg_type IS OBJECT ( FID NUMBER, geom MDSYS.SDO_GEOMETRY);

CREATE OR REPLACE TYPE fg_table IS TABLE OF fg_type;

--create test table
CREATE TABLE pgeoms 
(ID   NUMBER,
 geom MDSYS.SDO_GEOMETRY);
 
INSERT INTO pgeoms VALUES
 (1, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(  1,  5,  0), NULL,NULL));

INSERT INTO pgeoms VALUES
 (2, SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(  3,  7,  0), NULL,NULL));

COMMIT;

SELECT * FROM pgeoms;

ID      GEOM
---------------------------------
1	(2001; ; (1; 5; 0); ; )
2	(2001; ; (3; 7; 0); ; )

--example procedure
DECLARE
   sdpt fg_table;

BEGIN
   
   --BULK COLLECT geometry into collection
   SELECT fg_type(id, geom) BULK COLLECT INTO sdpt FROM pgeoms;
   
   --modify X, Y
   FOR i IN sdpt.first .. sdpt.last 
   LOOP
     NULL;
     sdpt(i).geom.SDO_POINT.X := sdpt(i).geom.SDO_POINT.X + 100;
     sdpt(i).geom.SDO_POINT.Y := sdpt(i).geom.SDO_POINT.Y + 200;
   END LOOP;
   
   --update table
   UPDATE pgeoms pg SET geom =(SELECT geom FROM table(sdpt) WHERE pg.id=fid);
   
END;

SELECT * FROM pgeoms;

ID      GEOM
---------------------------------
1	(2001; ; (101; 205; 0); ; )
2	(2001; ; (103; 207; 0); ; )

ROLLBACK;