English

Problem: Cannot add rules to a topology stored in ArcSDE for Oracle

Description

When adding a topology rule to a topology class stored in ArcSDE for Oracle, the rule is not added. No error messages appear to indicate a failure.

Cause

A possible cause is the primary key constraint on the ID column of the GDB_VALIDRULES tables is being violated.

This occurs when the sequence which generates the ID values for this column becomes out of sync with the existing values in the column. This situation does not occur under normal circumstances, but possible causes include improper use of the Oracle import function, an Oracle shutdown during an ArcGIS transaction, or manual manipulation of the ArcSDE metadata tables.

If verbose logging is enabled for the sde.errlog, the following error is returned:

db_sda_execute_stmt::OCIStmtExecute (1)
.SDE_Oracle Error: 1
ORA-00001: unique constraint (SDE.GDB_VR_PKC) violated

Solution or Workaround

Advance the sequence to a higher value than the maximum value in the ID column of SDE.GDB_VALIDRULES. Log in to SQLPlus as the SDE user to run the following SQL commands.

  1. Determine the maximum value in the ID column of SDE.GDB_VALIDRULES.
    Code:
    SQL>SELECT MAX(id) FROM gdb_validrules;

    Result for this example:

    MAX(ID)
    ---------
    50
  2. Determine the name of the sequence for the ID column. The naming convention is R<registration id> where <registration id> is the value in the registration_id column of SDE.VALIDRULES for the entry for the TABLE_REGISTRY TABLE.
    Code:
    SQL>SELECT registration_id FROM table_registry WHERE table_name = 'GDB_VALIDRULES';

    Result for this example:

    REGISTRATION_ID
    ---------------
    8
  3. Determine the next value to be supplied by the sequence:
    Code:
    SQL> select last_number from user_sequences where sequence_name = 'R8';

    Result for this example:

    LAST_NUMBER
    -----------
    42

    In this example, The next ID that is provided by the sequence is 42. Since the highest ID in GDB_OBJECTCLASSES is currently 50, any values lower than 50 are already taken. Therefore, ArcSDE attempts to insert a non-unique value into the column, violating the primary key constraint.
  4. In this example, the sequence needs to be advanced by nine. Therefore, run the following SQL statement nine times:
    Code:
    SQL> select r8.nextval from dual;

    Result for this example:

    NEXTVAL
    ----------
    51

    Since the next value to be supplied by the sequence is higher than the current maximum value in the column, it should now be possible to add rules to the topology.

Related Information