Use SQL to insert a value into a Global ID or GUID column in an Enterprise Geodatabase, versioned views in Oracle

Summary

Instructions provided describe how to insert a value into a Global ID or Globally Unique Identifier (GUID) column in an Enterprise Geodatabase versioned view using Structured Query Language (SQL) for Oracle.

Procedure

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. This is how features are tracked in one-way and two-way geodatabase replication and are used in relationships or in any application requiring globally unique identifiers.

In a relationship, if a Global ID field is the origin key, a GUID field must be the destination key. Add Global IDs to a feature class in ArcCatalog by right-clicking ArcCatalog and clicking Add Global IDs. The geodatabase maintains these values automatically. Create the GUID field in ArcCatalog and maintain its values.

When inserting a row into a versioned view with a Global ID column using SQL, provide a unique GUID value for the Global ID column.

Note:
For more information related to Global IDs, GUIDs, and Geodatabase Replication, refer to the ArcGIS Desktop Help in the Related Information section.

1. While logged in as the SDE user, execute the SQL below to create a function in the Relational Database Management System (RDBMS) to generate a GUID.
   
   This function creates a GUID using the Oracle SYS_GUID function and parses the result into the Microsoft uncompressed or standard GUID format.

CREATE OR REPLACE FUNCTION GDB_GUID
RETURN NCHAR
IS
guid NCHAR (38);
BEGIN
guid := upper(RAWTOHEX(SYS_GUID()));
RETURN
'{'||substr(guid,1,8)||'-'||substr(guid,9,4)||'-'||substr(guid,13,4)||'-'||substr(guid,17,4)||'-'||substr(guid,21,12)||'}';
END;
/

2. Grant permissions for the function as the SDE user to execute the function, while logged in as a user other than SDE.

grant execute on gdb_guid to public;

3. Create the versioned view.

"geodatabase administrator" table -o create_mv_view -T parcels_v -t parcels -i <port> -u <user> -p <password>

Note:
For more information on creating and using versioned views in Enterprise Geodatabase, refer to the ArcGIS Desktop Help.

4. Find the REGISTRATION_ID of the versioned feature class from which the view was created.

select registration_id,table_name from sde.table_registry where
table_name='PARCELS';

REGISTRATION_ID TABLE_NAME
--------------- ---------------------
            113 PARCELS

5. For Spatial Geometry types, find the Spatial Reference ID (SRID) of the versioned feature class from which the view was created.

select srid from sde.st_geometry_columns where table_name='PARCELS' and owner='USER1';

      SRID
----------
         7

6. Create a new version to edit the versioned view. If using an existing version, skip this step and go to the next step.
   
   Unlike editing versioned data in an ArcGIS edit session, no internal version reconciliation is done with these edits. Therefore, it is strongly recommended that versioned views not be used to edit the default version or any version that may be subject to simultaneous editing or reconciliation by other users, because conflicts will not be detected. Instead, create a version specifically for editing with versioned views.
   
   To create a new version:

   --Create a new version.
   variable mvvers nvarchar2(97);
   
   exec :mvvers := 'PARCELS_42';
   
   exec sde.version_user_ddl.create_version('SDE.DEFAULT',:mvvers,sde.version_util.C_take_name_as_given,sde.version_util.C_version_public,'Parcels WorkOrder 42 Version');

7. Insert a record into the versioned view as the owner of the versioned table.

Note:
For more information on editing with versioned views in Enterprise Geodatabase, refer to the ArcGIS Desktop Help.

Note:
For more information on the ST_GEOMETRY type and inserting Geometry in Enterprise Geodatabase, refer to the ArcGIS Desktop Help.

--Set the version to edit.
exec sde.version_util.set_current_version('USER1.PARCELS_42');

--Start the edit session.
exec sde.version_user_ddl.edit_version('USER1.PARCELS_42',1);

--Perform the desired insert.

--For Spatial GEOMETRY Types a shape may be inserted.
insert into parcels_v (objectid,globalid,shape) values
(sde.version_user_ddl.next_row_id('USER1',113),sde.GDB_GUID,(sde.st_polygon
('polygon ((10000 520000, 100008889 55000, 1045545983 234280934, 10000 520000))',7)));

--For Non-Spatial GEOMETRY Types (Binary, LOB, etc.) a NULL shape MUST be inserted:
insert into parcels_v (objectid,globalid,shape) values
(sde.version_user_ddl.next_row_id('USER1',113),sde.GDB_GUID, NULL);

--Stop the edit session.
exec sde.version_user_ddl.edit_version('USER1.PARCELS_42',2);

--Commit the edit.
commit;