English

How To: Populate an ArcSDE maintained row ID column when inserting a new ST_Geometry feature with SQL in Oracle

Summary

When data is stored in ArcSDE using the ST_Geometry type, it is possible to insert new simple features using Structured Query Language (SQL). If the layer being updated has an ArcSDE maintained row ID, use the next_row_id function from the sde.version_user_ddl package to generate a new, unique value for the row ID. The example below is from an Oracle database.

Procedure

The next_row_id function used to generate row IDs takes two parameters: owner and registration_id. Owner is the name of user who owns the table. Registration_id comes from the repository table sde.table_registry. With the table owner name and the registration ID, a SQL statement can be created that populates the row ID. The example below is from an Oracle database and uses a layer named 'parks owned' by a user named 'gisdata'.

  1. Find the registration_id for the parks feature class owned by the gisdata user:

    SQL> SELECT registration_id FROM sde.table_registry WHERE table_name = 'PARKS' and owner = 'GISDATA';

    REGISTRATION_ID
    ---------------
    759

  2. SQL to be executed:

    SQL> INSERT INTO parks (OBJECTID,SHAPE) VALUES (sde.version_user_ddl.next_row_id('GISDATA', 759),
    ST_GEOMETRY('point (2790683.61 1130335.86)', 3));

    1 row created.


    Note:
    ArcGIS Desktop only draws types of ST_Geometry, but does not draw any of the subclasses of ST_Geometry. If a layer within ArcGIS Desktop is being used, use the ST_Geometry constructor, not ST_Point, or any of the other subclasses.

Related Information