Desktop Help 10.0 - Editing nonversioned geodatabase data in Oracle using SQL

You can use SQL to update, insert data into, and delete data from nonversioned tables in the geodatabase if they do not participate in geodatabase behavior. See What type of data can be edited using SQL? for information on the types of data and geodatabase behavior you cannot edit with SQL.

All data that is registered with the geodatabase has a system-maintained, unique, not-null object ID (row ID) column. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the object ID.

Use the next_row_id function from the sde.version_user_ddl package to generate a new, unique value for the object ID.

The next_row_id function uses two parameters: owner and registration ID. Owner is the name of the user who owns the table. The registration ID comes from the TABLE_REGISTRY ArcSDE system table. With the table owner name and the registration ID, a SQL statement can be created to populate the object ID column.

Note:

The next available object ID is not necessarily the next sequential number after the last inserted object ID. Object IDs for some client applications are assigned in batches, so the next available object ID may be many numbers higher than the last one you used. Also, any unused object IDs from the batch are returned to the pool of available values, which means the next available object ID value may actually be a lower number than the one you inserted last.

Steps:

Log in to the database from a SQL editor such as SQL*Plus.

Be sure to log in to the database as a user who has permission to edit the data.
Query the TABLE_REGISTRY system table to determine the registration ID of the business table to which a row is to be inserted.
In this example, the table to be edited is the business table of the STREAMS feature class, and the owner is ENG2.
```
SELECT registration_id 
FROM sde.table_registry 
WHERE table_name = 'STREAMS' and owner = 'ENG2';

REGISTRATION_ID
131
```
The registration ID for the ENG2.STREAMS table is 131.
Include the sde.version_user_ddl.next_row_id function with the owner name and registration ID in the INSERT statement to insert the next available value into the object ID column.
In this example, an ST_Geometry line segment is inserted in the STREAMS feature class.
```
INSERT INTO eng2.streams (OBJECTID,NAME,SHAPE) VALUES
(sde.version_user_ddl.next_row_id('ENG2', 131), 
'TRANQUIL', 
sde.ST_GEOMETRY('linestring (750 150, 750 750)', 4) 
);
```
You can continue editing or, if you are done editing, commit your edits to the database.

Editing nonversioned geodatabase data in Oracle using SQL

Related Topics