How To: Convert an Oracle SDO attribute to an st_geometry attribute
Instructions provided describe how to convert Oracle spatial SDO attributes to st_geometry attributes.
Below are two examples of how to convert Oracle spatial attributes to st_geometry. The first example demonstrates how to select all features from a table with an Oracle spatial attribute and insert the features into a table with an st_geometry attribute. The second example demonstrates using a trigger on a table with an Oracle spatial attribute that is executed after each insert and writes the feature to a second table that uses an st_geometry attribute.
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.
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.
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.
SQL> INSERT INTO parcels_st SELECT r121.nextval, sde.st_geomfromwkb(sdo_geometry.get_wkb(shape),3) FROM parcels_sdo;
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).
CREATE OR REPLACE TRIGGER sdo_to_st AFTER INSERT ON parcels_sdo FOR EACH ROW
INSERT INTO parcels_st VALUES (r121.nextval, sde.st_geomfromwkb(sdo_geometry.get_wkb(:NEW.shape),3));
WHEN OTHERS THEN