BUG

Editing an archive enabled table with SQL through a multi-version view in the DEFAULT version does not maintain the table's archive

Last Published: April 25, 2020

Description

Editing an archive enabled table with SQL through a multi-version view referencing the DEFAULT version does not maintain the table's archive class.

For example, if an application using SQL starts editing the sde.DEFAULT version by calling the database procedure sde.version_user_ddl.edit_version, updates a few rows and proceeds to stop editing by calling sde.version_user_ddl.edit_version, the changes are not archived to the archive class.

Cause

When using SQL to modify a versioned table through a multi-version view, the DML (insert, update or delete) is applied directly to the delta tables for the state the version references. Because the changes are being applied directly to the version being edited, there's no ability for the archiving operation to archive the changed objects to the archive class.

Since the changes are not correctly archived, future edits to these same objects can lead to inconsistencies in the archive class.

Workaround

If using SQL to update an archive enabled table is required, never edit the DEFAULT version directly. Edit any other version of the geodatabase and use reconcile/post to apply the changes to the DEFAULT version. Using ArcGIS reconcile and post ensures the changes are correctly archived to the archive class.

The following Oracle SQL*Plus example demonstrates editing a multi-version view in a private version named 'WORK ORDER'.

Code:
SQL> EXEC sde.version_user_ddl.edit_version('WORK ORDER',1);

PL/SQL procedure successfully completed.

SQL> UPDATE admin.parcels_view SET usage = 'REC' WHERE apn = '10-ARC-9842';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> EXEC sde.version_user_ddl.edit_version('WORK ORDER',2);

PL/SQL procedure successfully completed.

Once the changes have been applied, the version can then be reconciled and posted with ArcGIS. Posting with ArcGIS ensures the changes to the parcels archive class are correctly applied.

    Article ID:000010319

    Software:
    • Legacy Products

    Receive notifications and find solutions for new or common issues

    Get summarized answers and video solutions from our new AI chatbot.

    Download the Esri Support App

    Discover more on this topic

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options