English

How To: Use SQL to insert a value into a Global ID or GUID column in ArcSDE multiversion views in Oracle

Summary

Instructions provided describe how to insert a value into a Global ID or Globally Unique Identifier (GUID) column in an ArcSDE multiversion 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 multiversion 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 installed with ArcGIS Desktop or the link available in Related Information.

Examples provided are specific to Oracle, but the steps are similar for other supported databases.

  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.

    Code:
    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.

    Code:
    grant execute on gdb_guid to public;


  3. Create the multiversion view.

    Code:
    sdetable -o create_mv_view -T parcels_v -t parcels -i <port> -u <user> -p <password>


    Note:
    For more information on creating and using multiversion views in ArcSDE, refer to the ArcGIS Desktop Help.

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

    Code:
    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.

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

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


  6. Create a new version to edit the multiversion view. If using an existing version, skip this step and go to Step 7.

    Unlike editing versioned data in an ArcGIS edit session, no internal version reconciliation is done with these edits. Therefore, it is strongly recommended that multiversioned 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 multiversioned views.

    To create a new version:

    Code:
    --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 multiversion view as the owner of the versioned table.

    Note:
    For more information on editing with multiversion views in ArcSDE, refer to the ArcGIS Desktop Help.

    In the example below:
    'USER1' is the owner of the versioned table, and '113' is the registration_id found in Step 4 above.

    For Spatial Geometry types, the Spatial column value depends on the type of geometry being inserted as well as the points to insert. '7' is the SRID of the Versioned table found in Step 5 above.

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


    Code:
    --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;

Related Information