There Be Dragons – Uncharted Internet Mapping

Ok, I really struggled with this one. I have a CLOB field in my database that has all the LAT/LON coordinate pairs stored.  I wanted to harvest this – with a trigger- to populate my SDO_GEOM field in Oracle.  The trick is, you can’t just EVALUATE the column.  You have to build a SQL statement and then insert it into the column in the trigger: – this example is for POLYLINES…

CREATE OR REPLACE TRIGGER CRAZYTABLE
BEFORE INSERT OR UPDATE
OF FEATURE_TYPE
ON CRAZYTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
myCoords CLOB;
mySQL VARCHAR2(4000);
myGEOM SDO_GEOMETRY;

BEGIN
   myCoords := :OLD.COORDS;

        mySQL := ‘select MDSYS.SDO_GEOMETRY(3002, 8307, null, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1),MDSYS.SDO_ORDINATE_ARRAY(‘  || myCoords || ‘)) from dual’;
        
         EXECUTE IMMEDIATE mySQL into myGEOM;
         
      :NEW.GEOMCOL := myGEOM;    

END;
/

This works like a charm.  whew!