Editing versioned data in Oracle using multiversioned views and SQL

You can edit versioned attribute data in a geodatabase in Oracle by editing a multiversioned view created on the data. Beginning with ArcGIS 10 SP2, there are two ways you can do this: editing a named version or editing the DEFAULT version. See A quick tour of editing versioned data using SQL for a description of each editing model.

There are several stored procedures installed with ArcSDE that help you work with multiversioned views to edit a named version (versions other than DEFAULT). These components and their descriptions are as follows:

Stored procedure	Purpose	Syntax and description
sde.version_user_ddl.create_version	Creates a named geodatabase version. If you are editing named versions, always edit your own, separate version; multiple editors cannot edit the same version using multiversioned views. Do not use this procedure if you are editing the DEFAULT version.	EXEC sde.version_user_ddl.create_version ('<parent_version>', :<child_version_variable>, <name_rule>, <access>, '<description>') <parent_version> is the version from which your version is created. <child_version> is the name for the version you are creating. <access> is the permission level for the version: 0 for Private, 1 for Public, or 2 for Protected. <description> is a text description of the child version.
sde.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 called setcurrentversion. Do not use this procedure if you are editing the DEFAULT version.	EXEC sde.version_util.set_current_version('<version_name>') The <version_name> is the name of the geodatabase version to which you want to connect.
sde.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 called.	EXEC sde.version_user_ddl.edit_version('<version_name>',<1 or 2>) Specify 1 to start the edit session. Specify 2 to end it.
sde.version_user_ddl.delete_version	Delete a named geodatabase version.	EXEC sde.version_user_ddl.delete_version('<version_name>')

When you edit versioned tables through multiversioned views, changes are made to the delta tables, and row ID (ObjectID) values for new records are automatically generated. However, unlike editing versioned data in an ArcGIS edit session, no internal version reconciliation is done with these edits.

Caution:

• Version management operations, such as reconciling, resolving conflicts, and posting, must be done using ArcGIS software.
• Never use database management system (DBMS) tools to update any row ID (ObjectID) field maintained by ArcGIS in the database. These ObjectID fields are allocated and managed by the geodatabase and, therefore, should not be altered using SQL.

If you want to edit the DEFAULT version, create a multiversioned view, then edit that view using SQL. Do not set the version or start an edit session to edit the DEFAULT version. Be sure to commit changes to the database after each edit; exclusive locks are held on the delta tables until you commit, thereby closing the transaction.

The following steps guide you through editing a named geodatabase version. In the examples, the multiversioned view created to perform the edits on the table is code_mv, and the version created and used for editing is mvedits.

Tip:

Remember that the dataset you edit through a multiversioned view must already have been registered as versioned and you must have privileges in the database sufficient to edit the dataset and view.

Steps:

1. At an MS-DOS (Windows) or shell (UNIX or Linux) command prompt, execute the sdetable utility to create a multiversioned view.

   sdetable –o create_mv_view –T code_mv –t code_enf 
   –i sde:oracle11g –u sarja –p not4U@orasvc

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

   VARIABLE mv_version NVARCHAR2(10); 
   EXEC :mv_version := 'mvedits';

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

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

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

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

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

5. 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('mvedits',1);

6. Perform the first edit to the multiversioned view using SQL.

   In this example, an existing record is updated.

   UPDATE code_mv 
   SET propowner = 'C. Industry' 
   WHERE vid = 2233

7. Perform the next edit to the multiversioned view using SQL.

   In this example, a new record is inserted to the table.

   INSERT INTO code_mv (codenum,propowner,shape) 
   VALUES 
   (567, 'Anjo Badsu', sde.ST_PointFromText('point (40 40)', 12));

   Tip:

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

   Although it is possible to version a table that has only been registered with ArcSDE and has a user-maintained row ID column, this is not recommended. If you do this anyway, though, you must provide a row ID with the INSERT statements; the multiversioned view cannot generate a value for you.

8. Execute a COMMIT or ROLLBACK statement.
9. 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('mvedits',2);

10. When all editing is done, reconcile and post edits through ArcGIS Desktop, then you can edit the geodatabase version you created in step 3. Or if you decide you do not want the changes, you can delete the version you created in step 3 without reconciling and posting the edits.

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