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!