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.

TipTip:

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.

Steps:
  1. 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.

  2. Open a SQL editor, such as SQL*Plus.
  3. Connect to the database as a user who has permissions to edit the table.
  4. 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.

  5. 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.
  6. 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)));
    
  7. When you have completed your edits, commit them to the database.
  8. 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.

2/5/2013