27
Nov
08

Another Tricky Trigger

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!

Advertisements

0 Responses to “Another Tricky Trigger”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Gordon Luckett

Arrow Geomatics Inc's Gordon Luckett

Contact

gordon dot luckett at arrowgeo dot com 1-519-837-9500 (Arrow Geomatics Inc.)

Checkout MapGuide Guy’s Youtube Channel

gordonluckett@twitter


%d bloggers like this: