English

How To: Create a new version in Oracle using the version_user_ddl stored procedure

Summary

To create a new version using the sde.version_user_ddl procedure, it requires an output variable for the new version being created.

If using PL/SQL, the IN/OUT argument for the name must be a variable. This is also true when using SQL*Plus; a variable must be declared prior to executing the stored procedure.

Procedure

To create a new version in SQL*Plus, declare a variable, set the value for the variable and then execute the sde.version_user_ddl procedure.

The following example demonstrates creating a variable named version_name, setting the value of the variable to the name of the version which is created 'WORKORDER 1632', and then executing the stored procedure.

Code:
SQL> VARIABLE version_name NVARCHAR2(97);
SQL> EXECUTE :version_name := 'WORKORDER 1632';

PL/SQL procedure successfully completed.

SQL> PRINT version_name

VERSION_NAME
----------------------------------------------------------------------
WORKORDER 1632

SQL> exec sde.version_user_ddl.create_version('SDE.DEFAULT',:version_name,sde.version_util.C_take_name_as_given,sde.version_util.C_version_private,'Work order for pole replacement');

PL/SQL procedure successfully completed.

C:\>sdeversion -o describe -u user1 -p user1 -i 5151 -V "WORKORDER 1632"

ArcSDE 9.2 for Oracle10g Build 237 Mon May 28 12:01:00 2007
Version Administration Utility
-----------------------------------------------------
Work order for pole replacement
------------------------------------------------------------
Version Name: USER1.WORKORDER 1632
Version ID: 2000
Parent Version Name: SDE.DEFAULT
Parent Version ID: 1
State ID: 33263
Access: Private
Creation Time: 05/29/07 09:39:11

The sde.version_user_ddl.create_version procedure has the following definition:

Code:
PROCEDURE CREATE_VERSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARENT_NAME NVARCHAR2(97) IN
NAME NVARCHAR2(97) IN/OUT
NAME_RULE BINARY_INTEGER IN
ACCESS BINARY_INTEGER IN
DESCRIPTION NVARCHAR2 IN

The sde.version_user_ddl.create_version procedure contains the following constants:

For the name argument:

· sde.version_util.C_take_name_as_given attempts to use the name provided and if a version exists owned by the user, an error will be encountered.
· sde.version_util.C_generate_unique_name ensures the input name is unique if there is an existing version with the same name owned by the user. If the objective is to ensure a version is created, use the C_generate_unique_name constant and check the OUT argument value for name.

For the access argument:

· sde.version_util.C_version_private creates the version as private. Only the owner and SDE administrator are able to access the version.
· sde.version_util.C_version_public creates the version as public. All users are able to access the version.
· sde.version_util.C_version_protected creates the version as protected. All users may view the version, but only the owner and SDE administrator may edit the version.