Inserting a value into a global ID or GUID column in Oracle using SQL
Global ID and GUID data types store registry-style strings consisting of 36 characters enclosed in curly brackets. These strings uniquely identify a feature or table row within a geodatabase and across geodatabases. Global IDs are used to maintain uniqueness of records in one-way and two-way geodatabase replicas. GUIDs can be added to any layer.
Therefore, if the table you want to edit participates in replication or contains a GUID column, you must insert a unique value to the global ID or GUID column when you insert a new record to the table using SQL. To do this, call the sde.version_user_ddl.retrieve_guid utility. If the table you are editing is versioned, use the sde.version_user_ddl.retrieve_guid utility to edit a multiversioned view of the table using SQL.
You can add global IDs to a feature class in the Catalog window by right-clicking the dataset and clicking Add Global IDs. The geodatabase maintains these values automatically when editing through ArcGIS.
If you are editing a versioned table, open an MS-DOS or shell prompt and execute the sdetable command to create a multiversioned view.
See Creating multiversioned views for instructions.
- Open a SQL editor, such as SQL*Plus.
- Connect to the database as a user who has permissions to edit the table.
If the table or the table on which the view is based contains an ST_Geometry column, query the ST_GEOMETRY_COLUMNS table to find the spatial reference ID (SRID) of the table.
SELECT srid FROM sde.st_geometry_columns WHERE table_name='MYTABLE' AND owner='USER22'; SRID ---------- 4
Any records you insert to this view or table should use this SRID.
- If you are editing a versioned table, follow the instructions in Editing versioned data in Oracle using multiversioned views and SQL to create a version for editing, set the version, then start an edit session. If you are editing a nonversioned table, proceed to the next step.
When inserting a record, use the sde.version_user_ddl.retrieve_guid utility to insert the next global ID or GUID value.
In this example, a record is inserted to the multiversioned view, mytable_mv. The table contains an ST_Geometry column and uses the SRID obtained from step 4.
INSERT INTO mytable_mv (globalid,shape) VALUES (sde.version_user_ddl.retrieve_guid,(sde.st_polygon ('polygon ((10000 520000, 100008889 55000, 1045545983 234280934, 10000 520000))',4)));
- When you have completed your edits, commit them to the database.
- When you finish editing, stop the edit session as described in Editing versioned data in Oracle using multiversioned views and SQL if you were editing a versioned table.