topic Re: Edit default version using SQL Developer in Developers Questions

Edit default version using SQL Developer

MarcCusumano — Sat, 11 Dec 2021 19:22:10 GMT

Hello all. We are trying to create a nightly script that will set attachment flags, indicate a feature has an attachment available. This is not possible using hte ArcGIS front-end due to issues related to ArcFM AutoUpdaters (and also performance reasons), so we are attempting to develop a solution using SQL Developer (we cannot use MS Access or SQL Server due to incompatibilities with ST_GEOMETRY).

I am told that running an UPDATE query on a view will take care of updating all of the delta tables as well as the base table. The first feature I am running this on is a versioned view. Here is my code snippet:

EXECUTE sde.version_util.set_current_version ('SDE.DEFAULT');


EXECUTE sde.version_user_ddl.edit_version ('SDE.DEFAULT', 1);
UPDATE GASVALVE_VW
SET ATTACH_FLAG = 'N';
Commit;
EXECUTE sde.version_user_ddl.edit_version ('SDE.DEFAULT', 2);

However, I am being told I cannot edit the DEFAULT version:

Error starting at line 3 in command:

EXECUTE sde.version_user_ddl.edit_version ('SDE.DEFAULT', 1)

Error report:

ORA-20500: Cannot edit the DEFAULT version in STANDARD transaction mode.

ORA-06512: at "SDE.VERSION_USER_DDL", line 941

ORA-06512: at line 1

I am able to start an edit session in the DEFAULT version inside of ArcMap in the same GDB.

SQL Developer then tells me i cannot edit without being in an edit session (because the edit session could not be started by SQL Developer).

Error starting at line 4 in command:

UPDATE GASVALVE_VW

SET ATTACH_FLAG = 'N'

Error report:

SQL Error: ORA-20504: Editing the DEFAULT version is not supported because the spatial attribute is not a spatial type or the table is registered as versioned with the option to move edits to base. The session must call edit_version to start an edit session before editing the view.

ORA-06512: at "PSEG_GAS.V87_UPDATE", line 1

ORA-04088: error during execution of trigger 'PSEG_GAS.V87_UPDATE'

Not sure what I am doing wrong. Any help would be greatly appreciated!

Re: Edit default version using SQL Developer

AsrujitSengupta — Fri, 11 Sep 2015 15:50:14 GMT

An overview of editing versioned data using SQL—Help | ArcGIS for Desktop

Edit versioned data in Oracle using SQL—Help | ArcGIS for Desktop

Re: Edit default version using SQL Developer

MarcCusumano — Fri, 11 Sep 2015 17:51:46 GMT

Thanks Asrujit - it looks like I will have to create a version first, make the edits and then post. However, existing versions will not be able to see the edits, correct?

Re: Edit default version using SQL Developer

JoshuaBixby — Sat, 12 Sep 2015 14:20:20 GMT

Looking at the documentation that Asrujit SenGupta links to:

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.
....
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.

You are mixing the workflows for editing the Default version and editing named versions. If you want to edit Default directly, you do not need to create a named version.

Re: Edit default version using SQL Developer

MarcCusumano — Mon, 14 Sep 2015 12:14:30 GMT

Josh, if you read my original post, you would have seen that I am being told I cannot edit the default version. That was the whole point of my question.

Re: Edit default version using SQL Developer

AsrujitSengupta — Mon, 14 Sep 2015 15:05:53 GMT

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.

Re: Edit default version using SQL Developer

AsrujitSengupta — Mon, 14 Sep 2015 15:10:27 GMT

Also make sure that--

your data is not stored in the binary geometry storage type.
The table or feature class to be edited is not registered as versioned with the option to move edits to base
If the versioned view was created prior to ArcGIS 10.1, you must re-create it; older versioned views cannot be edited in the Default version.

Re: Edit default version using SQL Developer

JoshuaBixby — Mon, 14 Sep 2015 15:24:31 GMT

Marc Cusumano, I did read your original post, hence the comment you are mixing up the two different workflows. See Asrujit SenGupta's recent post for some great tips if you are still having problems.