Is This Content Helpful?
We're glad to know this article was helpful.
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';
SQL> SELECT srid FROM sde.st_geometry_columns WHERE table_name = 'PARCELS_ST';
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