Convert an Oracle SDO attribute to an st_geometry attribute

Example One

If the table with the st_geometry attribute is registered with the geodatabase, obtain the table's registration identifier to be used for the sequence to generate the ROWID value.

Code:
SQL> SELECT registration_id FROM sde.table_registry WHERE table_name = 'PARCELS_ST';

Obtain the table's spatial reference identifier from the st_geometry_columns table that is required as an argument value to the st_geomfromwkb constructor function.

Code:
SQL> SELECT srid FROM sde.st_geometry_columns WHERE table_name = 'PARCELS_ST';

Execute the INSERT statement, which uses the registration_id value 121 as the sequence name r121 (the sequence name r121 sets the ObjectID values), and the value 3 for the st_geometry spatial reference argument with the st_geomfromwkb constructor. The sdo_geometry.get_wkb function reads the Oracle spatial attribute and returns the feature as a well-known binary object that is then passed to the st_geomfromwkb function to generate the st_geometry feature.

Code:
SQL> INSERT INTO parcels_st SELECT r121.nextval, sde.st_geomfromwkb(sdo_geometry.get_wkb(shape),3) FROM parcels_sdo;

Example Two

As in the previous example, obtain the table's registration_id for the sequence name and the SRID value for the st_geomfromwkb spatial reference argument. The trigger is executed after every row that is inserted into the parcels_sdo table. If there are any errors during the execution of the trigger, the errors are ignored with the exception handler (this is to ensure the primary transaction is not impacted by the trigger specification).

Code:
CREATE OR REPLACE TRIGGER sdo_to_st AFTER INSERT ON parcels_sdo FOR EACH ROW

BEGIN

 INSERT INTO parcels_st VALUES (r121.nextval, sde.st_geomfromwkb(sdo_geometry.get_wkb(:NEW.shape),3));

 EXCEPTION
   WHEN OTHERS THEN
   NULL;

END;
/