Edit versioned data in Oracle using SQL

Complexity:

Beginner

Data Requirement:

Use your own data

You can edit versioned data in a geodatabase in Oracle in two ways: by editing a named version through a versioned view or editing the Default version through a versioned view. See An overview of editing versioned data using SQL for a description of each editing model.

In most cases, you will use only one editing model. However, it is possible to use both models at the same site; therefore, this workflow explains how to use both so you can see how you would transition between the two. If you use only one, read the section specific to the editing model you want to use.

Edit the Default version

You can use SQL to connect to the Default version and edit a versioned view. You do not need to open an edit session. When you start editing, you are automatically connecting to the current state of the Default version.

You should commit after each edit you make to avoid blocking other users from accessing the delta tables of the table or feature class.

In the following steps, a versioned view (blic_evw) is edited. Edits made to this view write to the delta tables of the blic versioned table. Since there are only a couple of edits, they will be made in the Default version.

1. Connect to the database from an SQL client. Be sure to connect as a user with privileges to edit the dataset and versioned view.
2. Perform the first edit to the versioned view using SQL.

   In this example, an existing row is updated to alter the property owner name.

   UPDATE blic_evw 
   SET propowner = 'C. O. Industry' 
   WHERE lic = 44977;
   

3. Commit your edit.

   COMMIT;
   

4. Do the next edit.

   A new record is added to a table through the versioned view. The table contains an ST_Geometry column (location).

   INSERT INTO blic_evw (propowner, date_entered, location)
   VALUES (
     'Moe Esposito', 
     SYSDATE, 
     sde.ST_PointFromText ('point (0.0125662 0.0046711)', 12));
   

   Tip:

   In the INSERT statement, no ObjectID value is specified; the versioned view automatically gets the next available ObjectID and inserts it for the row.

5. Commit your edit.

   COMMIT;
   

Edit a named version

You are handed a stack of updates for the blic feature class, so you decide it would be a good idea to perform your edits in a named version.

The stored procedures and their syntax that you will use to edit a named version of the geodatabase are as follows:

Stored procedure	Purpose	Syntax and description
version_user_ddl.create_version	Create a named geodatabase version. If you are editing named versions (versions other than Default), always edit your own, separate version; multiple editors cannot edit the same named version using versioned views.	EXEC {sde | <schema owner>}.version_user_ddl.create_version ('<parent_version>', :<child_version_variable>, <name_rule>, <access>, '<description>')
version_util.set_current_version	Set which geodatabase version and state you will access. You access the state the version was pointing to when you executed set_current_version.	EXEC {sde | <schema owner>}.version_util.set_current_version('<child_version>')
version_user_ddl.edit_version	Start and end an edit session on a named version. Edits in the transaction are committed whenever this procedure is executed.	EXEC {sde | <schema owner>}.version_user_ddl.edit_version ('<child_version>',<1 or 2>) Specify 1 to start the edit session. Specify 2 to end it.

The parameters in the execute statements represent the following:

• {sde | <schema owner>} indicates which user owns the procedure. If you are executing the procedure for the master sde geodatabase, use sde; if you are executing the procedure for a geodatabase in another user's schema, specify that user's name.
• <parent_version> is the version from which your version is created.
• <child_version> is the named version you create for editing.
• <name_rule> indicates whether the name specified for the version should be taken as given (2), or a unique name should be created when a duplicate name is specified (1).
• <access> is the permission level for the version: either 0 for private, 1 for public, or 2 for protected.
• <description> is a text description of the child version.

In the following steps, a named version (myedits12111) is created from Default and edits are made in that version through the blic_evw versioned view.

1. Open a SQL client and declare a variable to store the version you will create in the next step.

   VARIABLE my_version NVARCHAR2(10); 
   EXEC :my_version := 'myedits12111';
   

   In this example, my_version is the variable name, NVARCHAR2(10) is the data type of the variable, and myedits12111 is the version name.

2. Create a new version in which to perform your edits.

   EXEC sde.version_user_ddl.create_version 
   ('sde.DEFAULT', :my_version, sde.version_util.C_take_name_as_given, sde.version_util.C_version_private, 'versioned view edit version');
   

3. Set the version for the edit session to the child version you just created.

   EXEC sde.version_util.set_current_version('myedits12111');
   

4. Start an edit session by executing the version_user_ddl.edit_version stored procedure and specifying 1.

   The 1 indicates an edit session should be started.

   EXEC sde.version_user_ddl.edit_version('myedits12111',1);
   

5. Do a set of edits on the versioned view using SQL.

   INSERT INTO blic_evw (propowner, date_entered, location)
   VALUES (
     'V. Nay', 
     SYSDATE, 
     sde.ST_PointFromText ('point (0.0125699 0.0046700)', 12));
   
   UPDATE blic_evw 
   SET phone1='555.111.2345', phone2='555.111.6789' 
   WHERE propowner = 'Tella Chomsky';
   
   UPDATE blic_evw
   SET exp_date = '2020-06-30'
   WHERE lid = 22903;
   
   UPDATE blic_evw
   SET location = sde.ST_PointFromText ('point (0.0125705 0.0046688)', 12))
   WHERE lid = 8992;
   
   INSERT INTO blic_evw 
   (codenum, propowner) 
   VALUES (456, 'Anjo Badsu');
   
   DELETE FROM blic_evw
   WHERE lid = 133;
   

6. Commit your edits.

   COMMIT;
   

More edits are made in SQL. After the next set of edits is finished, close the edit session.
7. Stop the edit session by executing the version_user_ddl.edit_version stored procedure, but this time, specify 2.

   The 2 indicates the edit session should be closed.

   EXEC sde.version_user_ddl.edit_version('myedits12111',2);
   

   Tip:

   You can only edit one version per edit session. For example, if you want to edit a second version (such as mynewedits), you must close the edit session on myedits12111, as shown in this step. Then you could execute set_current_version to point to version mynewedits, then start an edit session on it by executing edit_version.

Switch from editing a named version back to editing Default

If you need to go back to editing the Default version directly—for example, if your manager tells you other users at your site need to see a particular edit immediately—you can execute version_user_ddl.set_default to switch back to editing the Default version. The edits you make to the Default version can be seen by other users connected to Default as soon as they refresh their client connections.

The stored procedure and its syntax that you will use to edit the Default version of the geodatabase are as follows:

Stored procedure	Purpose	Syntax and description
version_util.set_default	Return to editing the current state of the Default version. Execute this procedure if you were editing a named version or a specific state of the Default version but want to go back to editing the current version of Default.	EXEC {sde | <schema owner>}.version_user_ddl.set_default

1. At the SQL prompt, execute the set_default procedure.

   EXEC sde.version_util.set_default;
   

   The session is now set to edit the current state that the Default version references.

2. Edit the blic_evw versioned view.

   INSERT INTO blic_evw (propowner, b_name, date_entered, location)
   VALUES (
     'Ruben Gol', 
     SYSDATE, 
     'Chez Coiffure',
     sde.ST_PointFromText ('point (0.0125650 0.0046721)', 12));
   

3. Commit your edit.

   COMMIT;
   

   Other clients that are pointing to the same state of the Default version or any state in its lineage can refresh their connections and see the new point feature.

Reconcile and post the edits to the named version

When you have finished the edits in the named version, you must use ArcGIS to reconcile, review and resolve conflicts, and post your edits. This can be done in ArcGIS for Desktop using the Reconcile Versions geoprocessing tool or a Python script. See the following topics for more information:

• A quick tour of reconciling a version
• Reconciling a version
• A quick tour of reviewing conflicts
• Posting changes
• Reconcile Versions

Tip:

If the data you were editing was registered as versioned with the option to move edits to base, the edits will be moved to the base table after you reconcile and post to the Default version.

Once edits have been posted to the Default version, your named version can be deleted. The stored procedure and its syntax that you will use to delete the named version after you reconcile and post to the Default version of the geodatabase are as follows:

Stored procedure	Purpose	Syntax and description
version_user_ddl.delete_version	Delete a geodatabase version.	EXEC {sde | <schema owner>}.version_user_ddl.delete_version('<child_version>')

The following example deletes myedits12111 after all edits have been reconciled and posted to the Default version:

EXEC sde.version_user_ddl.delete_version('myedits12111');