1 Reply Latest reply: Mar 5, 2014 12:25 PM by Ben Speckhard-Oracle RSS

    ORA-20092: cannot UPDATE because locking is on and row is already versioned

    718718c8-314d-4af7-8b47-97269e1414a7

      We are trying to update records in a table on a child of a child workspace.  Below is our hierarchy of workspaces:

       

      LIVE – Standard parent workspace, oracle folks should that this is the top workspace in the hierarchy

      PREVIOUS – child of LIVE workspace

      CURRENT – child of PREVIOUS workspace

       

      There were also child workspaces of CURRENT, but we rolled them back and removed them in order to avoid any conflicts.

       

      As I stated above, we are trying to update records in a table on a child of a child workspace;  which would be the CURRENT workspace in this case.  We are receiving the following error:

       

      Error report -

      ORA-20092: cannot UPDATE because locking is on and row is already versioned

      ORA-06512: at "MGFEE.OVM_UPDATE_762", line 7

      ORA-04088: error during execution of trigger 'MGFEE.OVM_UPDATE_762'

      ORA-06512: at line 7

       

      Specifically, we are trying to update FACILITY_TYPE_ID on a table called ROAD_PIECE.  I've put the trigger text in question "OVM_UPDATE_762" below.  Is there any help with this?

       

      CREATE OR REPLACE TRIGGER MGFEE.OVM_Update_762 INSTEAD OF

        UPDATE ON MGFEE.ROAD_PIECE_BASE FOR EACH ROW DECLARE dummy INTEGER;

        curver   INTEGER;

        crStatus VARCHAR2(20);

        isRef    INTEGER;

        l_state  VARCHAR2(30);

        lockmode VARCHAR2(2);

        l_rowid2 rowid := :old.wm_rowid ;

        l_ltlock           VARCHAR2(100) DEFAULT NULL;

        overwritemode_int  INTEGER := 1;

        par_lock_str       VARCHAR2(100);

        lock_status        INTEGER;

        wm$hasCRChild_var  BOOLEAN := false;

        prev_wm$hasCRChild BOOLEAN := false;

        prev_old_nextver   VARCHAR2(1000);

        prev_op_ctx        VARCHAR2(100);

        /*** needed for dynamic update statements ***/

        upd_stmt VARCHAR2(32000);

        rids wmsys.lt_ctx_pkg.rowid_tab ;

        /*** Begin needed for implementing RIC's ***/

        needToFire     BOOLEAN;

        nullsInFK      BOOLEAN;

        dependent_rows BOOLEAN;

        es_sql_string  VARCHAR2(32000);

        /*** End needed for implementing RIC's ***/

        N$FEATURE MDSYS.SDO_TOPO_GEOMETRY := :NEW.FEATURE;

        N$ROAD_PIECE_ID            NUMBER(9,0)       := :NEW.ROAD_PIECE_ID;

        N$ROAD_ID                  NUMBER(9,0)       := :NEW.ROAD_ID;

        N$CREATED_DATE             DATE              := :NEW.CREATED_DATE;

        N$MODIFIED_DATE            DATE              := :NEW.MODIFIED_DATE;

        N$CREATED_USERID           VARCHAR2(30)      := :NEW.CREATED_USERID;

        N$MODIFIED_USERID          VARCHAR2(30)      := :NEW.MODIFIED_USERID;

        N$TRANSPORTATION_PIECE_ID  NUMBER(9,0)       := :NEW.TRANSPORTATION_PIECE_ID;

        N$RANGE_SOURCE_CODE        CHAR(2)           := :NEW.RANGE_SOURCE_CODE;

        N$ADDRESS_INTERP_CODE      CHAR(2)           := :NEW.ADDRESS_INTERP_CODE;

        N$RANGE_ADDED_DATE         DATE              := :NEW.RANGE_ADDED_DATE;

        N$FROM_ADDRESS_LEFT        NUMBER(9,0)       := :NEW.FROM_ADDRESS_LEFT;

        N$FROM_ADDRESS_RIGHT       NUMBER(9,0)       := :NEW.FROM_ADDRESS_RIGHT;

        N$TO_ADDRESS_LEFT          NUMBER(9,0)       := :NEW.TO_ADDRESS_LEFT;

        N$TO_ADDRESS_RIGHT         NUMBER(9,0)       := :NEW.TO_ADDRESS_RIGHT;

        N$ZIPCODE_LEFT             CHAR(5)           := :NEW.ZIPCODE_LEFT;

        N$ZIPCODE_RIGHT            CHAR(5)           := :NEW.ZIPCODE_RIGHT;

        N$MALI_RD_SEG_RD_PIECE_ID  NUMBER(9,0)       := :NEW.MALI_RD_SEG_RD_PIECE_ID;

        N$ADDRESS_LEFT_PREFIX      CHAR(1)           := :NEW.ADDRESS_LEFT_PREFIX;

        N$ADDRESS_RIGHT_PREFIX     CHAR(1)           := :NEW.ADDRESS_RIGHT_PREFIX;

        N$NATIONAL_FOREST_ID       NUMBER(9,0)       := :NEW.NATIONAL_FOREST_ID;

        N$BOATING_ACCESS_SITE_CODE CHAR(8)           := :NEW.BOATING_ACCESS_SITE_CODE;

        N$STATE_PARK_CODE          CHAR(8)           := :NEW.STATE_PARK_CODE;

        N$BIA_OWNERSHIP_CLASS_CODE CHAR(1)           := :NEW.BIA_OWNERSHIP_CLASS_CODE;

        N$NHS_CODE                 CHAR(1)           := :NEW.NHS_CODE;

        N$NFC_CODE                 CHAR(2)           := :NEW.NFC_CODE;

        N$LEGAL_SYSTEM_CODE        CHAR(1)           := :NEW.LEGAL_SYSTEM_CODE;

        N$SINTMATCH                NUMBER(1,0)       := :NEW.SINTMATCH;

        N$SRATE                    NUMBER(9,0)       := :NEW.SRATE;

        N$SDIR                     NUMBER(9,0)       := :NEW.SDIR;

        N$FACILITY_TYPE_ID         NUMBER(4, 0)      := :NEW.FACILITY_TYPE_ID;

        N$CROSSOVER_ID             NUMBER(5,0)       := :NEW.CROSSOVER_ID;

        cur_time                   TIMESTAMP WITH TIME ZONE;

      PROCEDURE apply_unique_constraints

      IS

      BEGIN

        IF (wmsys.lt_ctx_pkg.rowlock_status NOT IN ('P', 'C', 'F')) THEN

          IF ((1=2) OR ((true) AND (N$TRANSPORTATION_PIECE_ID IS NOT NULL OR N$ROAD_PIECE_ID IS NOT NULL))) THEN

            INSERT

            INTO MGFEE.ROAD_PIECE_LCK VALUES

              (

                :NEW.ROAD_PIECE_ID,

                :NEW.TRANSPORTATION_PIECE_ID

              )

            RETURNING rowid

            INTO l_rowid2 ;

            DELETE MGFEE.ROAD_PIECE_LCK WHERE rowid=l_rowid2 ;

          END IF;

          IF (true) THEN

            IF (N$TRANSPORTATION_PIECE_ID IS NULL OR N$ROAD_PIECE_ID IS NULL) THEN

              BEGIN

                SELECT 1

                INTO dummy

                FROM DUAL

                WHERE EXISTS

                  (SELECT 1

                  FROM MGFEE.ROAD_PIECE_CONS cv

                  WHERE WM_delstatus             > 0

                  AND ( (TRANSPORTATION_PIECE_ID = N$TRANSPORTATION_PIECE_ID)

                  OR (TRANSPORTATION_PIECE_ID   IS NULL

                  AND N$TRANSPORTATION_PIECE_ID IS NULL ) )

                  AND ( (ROAD_PIECE_ID           = N$ROAD_PIECE_ID)

                  OR (ROAD_PIECE_ID             IS NULL

                  AND N$ROAD_PIECE_ID           IS NULL ) )

                  AND WM_ROWID                  != :NEW.WM_rowid

                  )

                AND NOT (N$TRANSPORTATION_PIECE_ID IS NULL

                AND N$ROAD_PIECE_ID                IS NULL) ;

                WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_232_NO,'MGFEE','IDX_ROAD_PIECE_TPID_ID');

              EXCEPTION

              WHEN NO_DATA_FOUND THEN

                NULL;

              END;

            ELSE

              BEGIN

                SELECT 1

                INTO dummy

                FROM DUAL

                WHERE EXISTS

                  (SELECT 1

                  FROM MGFEE.ROAD_PIECE_CONS cv

                  WHERE WM_delstatus          > 0

                  AND TRANSPORTATION_PIECE_ID = N$TRANSPORTATION_PIECE_ID

                  AND ROAD_PIECE_ID           = N$ROAD_PIECE_ID

                  AND WM_ROWID               != :NEW.WM_ROWID

                  )

                AND NOT (N$TRANSPORTATION_PIECE_ID IS NULL

                AND N$ROAD_PIECE_ID                IS NULL) ;

                WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_232_NO,'MGFEE','IDX_ROAD_PIECE_TPID_ID');

              EXCEPTION

              WHEN NO_DATA_FOUND THEN

                NULL;

              END;

            END IF;

          END IF;

        END IF;

      END;

      BEGIN

        prev_old_nextver             := wmsys.lt_ctx_pkg.old_nextver;

        wmsys.lt_ctx_pkg.old_nextver := :old.WM_nextver;

        prev_wm$hasCRChild           := wmsys.lt_ctx_pkg.wm$hasCRChild;

        prev_op_ctx                  := wmsys.lt_ctx_pkg.op_ctx;

        l_state                      := NVL(sys_context('lt_ctx', 'state'), 'LIVE');

        IF (wmsys.lt_ctx_pkg.version != -1) THEN

          WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_13_NO);

        END IF;

        wmsys.lt_ctx_pkg.checkFreezeStatus(l_state, curver, lock_status, crStatus, isRef);

        IF (crStatus                      = wmsys.lt_ctx_pkg.CRSTATUS_ALLCR OR crStatus = wmsys.lt_ctx_pkg.CRSTATUS_MIXED) THEN

          wmsys.lt_ctx_pkg.wm$hasCRChild := true;

        END IF;

        BEGIN

          lockMode    := wmsys.lt_ctx_pkg.lock_Mode;

          IF (l_state != 'LIVE' AND wmsys.lt_ctx_pkg.rowlock_status = 'X' AND wmsys.lt_ctx_pkg.current_locking_mode = wmsys.LT.OPTIMISTIC_LOCKING ) THEN

            DECLARE

              effectiveLockMode VARCHAR2(100) := wmsys.lt.getLockMode;

            BEGIN

              IF (effectiveLockMode IS NULL OR effectiveLockMode = 'C') THEN

                WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_171_NO, 'A topology feature table can be modified only in pessimistic mode - set locking ON');

              END IF;

            END;

          END IF;

          IF (wmsys.lt_ctx_pkg.rowlock_status != 'F') THEN

            IF ( wmsys.lt_ctx_pkg.dml_var      = 'ON' OR (wmsys.lt_ctx_pkg.triggerOpCtx != 'DML' AND ( wmsys.lt_ctx_pkg.tab_merge_wo_remove_var = 'ON' OR wmsys.lt_ctx_pkg.tab_merge_w_remove_var = 'ON' OR wmsys.lt_ctx_pkg.wspc_merge_wo_remove_var = 'ON' OR wmsys.lt_ctx_pkg.wspc_merge_w_remove_var = 'ON' OR wmsys.lt_ctx_pkg.table_refresh_var = 'ON' ))) THEN

              MGFEE.wm$TU_SEGMENT_io(N$FEATURE, :OLD.FEATURE, N$ROAD_PIECE_ID, :OLD.ROAD_PIECE_ID, N$ROAD_ID, :OLD.ROAD_ID, N$CREATED_DATE, :OLD.CREATED_DATE, N$MODIFIED_DATE, :OLD.MODIFIED_DATE, N$CREATED_USERID, :OLD.CREATED_USERID, N$MODIFIED_USERID, :OLD.MODIFIED_USERID, N$TRANSPORTATION_PIECE_ID, :OLD.TRANSPORTATION_PIECE_ID, N$RANGE_SOURCE_CODE, :OLD.RANGE_SOURCE_CODE, N$ADDRESS_INTERP_CODE, :OLD.ADDRESS_INTERP_CODE, N$RANGE_ADDED_DATE, :OLD.RANGE_ADDED_DATE, N$FROM_ADDRESS_LEFT, :OLD.FROM_ADDRESS_LEFT, N$FROM_ADDRESS_RIGHT, :OLD.FROM_ADDRESS_RIGHT, N$TO_ADDRESS_LEFT, :OLD.TO_ADDRESS_LEFT, N$TO_ADDRESS_RIGHT, :OLD.TO_ADDRESS_RIGHT, N$ZIPCODE_LEFT, :OLD.ZIPCODE_LEFT, N$ZIPCODE_RIGHT, :OLD.ZIPCODE_RIGHT, N$MALI_RD_SEG_RD_PIECE_ID, :OLD.MALI_RD_SEG_RD_PIECE_ID, N$ADDRESS_LEFT_PREFIX, :OLD.ADDRESS_LEFT_PREFIX, N$ADDRESS_RIGHT_PREFIX, :OLD.ADDRESS_RIGHT_PREFIX, N$NATIONAL_FOREST_ID, :OLD.NATIONAL_FOREST_ID, N$BOATING_ACCESS_SITE_CODE, :OLD.BOATING_ACCESS_SITE_CODE, N$STATE_PARK_CODE,

              :OLD.STATE_PARK_CODE, N$BIA_OWNERSHIP_CLASS_CODE, :OLD.BIA_OWNERSHIP_CLASS_CODE, N$NHS_CODE, :OLD.NHS_CODE, N$NFC_CODE, :OLD.NFC_CODE, N$LEGAL_SYSTEM_CODE, :OLD.LEGAL_SYSTEM_CODE, N$SINTMATCH, :OLD.SINTMATCH, N$SRATE, :OLD.SRATE, N$SDIR, :OLD.SDIR, N$FACILITY_TYPE_ID, :OLD.FACILITY_TYPE_ID, N$CROSSOVER_ID, :OLD.CROSSOVER_ID, l_rowid2, :old.wm_rowid);

            END IF;

            NULL;

          END IF;

          /* Make sure that the pkey is not being updated */

          IF :old.ROAD_PIECE_ID != N$ROAD_PIECE_ID OR N$ROAD_PIECE_ID IS NULL THEN

            WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_3_NO);

          END IF;

          IF (wmsys.owm_9ip_pkg.activeTimeForDML IS NULL) THEN

            cur_time                             := systimestamp;

          ELSE

            cur_time    := wmsys.owm_9ip_pkg.activeTimeForDML;

            IF (cur_time < :old.WM_createtime) THEN

              WMSYS.WM_ERROR.RAISEERROR(WMSYS.LT.WM_ERROR_171_NO, 'invalid active date for DML');

            END IF;

          END IF;

          apply_unique_constraints;

          IF (:old.WM_version = curver AND (NOT wmsys.lt_ctx_pkg.WoOverwriteMode)) THEN

            /* The row is in our current version */

            upd_stmt := ' begin if(true or :1 is null or :2 is null or :3 is null or :4 is null or :5 is null or :6 is null or :7 is null or :8 is null or :9 is null or :10 is null or :11 is null or :12 is null or :13 is null or :14 is null or :15 is null or :16 is null or :17 is null or :18 is null or :19 is null or :20 is null or :21 is null or :22 is null or :23 is null or :24 is null or :25 is null or :26 is null or :27 is null or :28 is null or :29 is null or :30 is null or :31 is null or :32 is null or :33 is null or :34 is null or :35 is null or :36 is null) then null; end if; update MGFEE.ROAD_PIECE_LT set ';

            upd_stmt := upd_stmt || 'FEATURE = :5,';

            upd_stmt := upd_stmt || 'ROAD_ID = :7,';

            upd_stmt := upd_stmt || 'CREATED_DATE = :8,';

            upd_stmt := upd_stmt || 'MODIFIED_DATE = :9,';

            upd_stmt := upd_stmt || 'CREATED_USERID = :10,';

            upd_stmt := upd_stmt || 'MODIFIED_USERID = :11,';

            upd_stmt := upd_stmt || 'TRANSPORTATION_PIECE_ID = :12,';

            upd_stmt := upd_stmt || 'RANGE_SOURCE_CODE = :13,';

            upd_stmt := upd_stmt || 'ADDRESS_INTERP_CODE = :14,';

            upd_stmt := upd_stmt || 'RANGE_ADDED_DATE = :15,';

            upd_stmt := upd_stmt || 'FROM_ADDRESS_LEFT = :16,';

            upd_stmt := upd_stmt || 'FROM_ADDRESS_RIGHT = :17,';

            upd_stmt := upd_stmt || 'TO_ADDRESS_LEFT = :18,';

            upd_stmt := upd_stmt || 'TO_ADDRESS_RIGHT = :19,';

            upd_stmt := upd_stmt || 'ZIPCODE_LEFT = :20,';

            upd_stmt := upd_stmt || 'ZIPCODE_RIGHT = :21,';

            upd_stmt := upd_stmt || 'MALI_RD_SEG_RD_PIECE_ID = :22,';

            upd_stmt := upd_stmt || 'ADDRESS_LEFT_PREFIX = :23,';

            upd_stmt := upd_stmt || 'ADDRESS_RIGHT_PREFIX = :24,';

            upd_stmt := upd_stmt || 'NATIONAL_FOREST_ID = :25,';

            upd_stmt := upd_stmt || 'BOATING_ACCESS_SITE_CODE = :26,';

            upd_stmt := upd_stmt || 'STATE_PARK_CODE = :27,';

            upd_stmt := upd_stmt || 'BIA_OWNERSHIP_CLASS_CODE = :28,';

            upd_stmt := upd_stmt || 'NHS_CODE = :29,';

            upd_stmt := upd_stmt || 'NFC_CODE = :30,';

            upd_stmt := upd_stmt || 'LEGAL_SYSTEM_CODE = :31,';

            upd_stmt := upd_stmt || 'SINTMATCH = :32,';

            upd_stmt := upd_stmt || 'SRATE = :33,';

            upd_stmt := upd_stmt || 'SDIR = :34,';

            upd_stmt := upd_stmt || 'FACILITY_TYPE_ID = :35,';

            upd_stmt := upd_stmt || 'CROSSOVER_ID = :36,';

            upd_stmt := upd_stmt || 'createTime=:3,

      ltlock=wmsys.lt_ctx_pkg.checkngetlock(wmsys.lt_ctx_pkg.lock_Mode, ltlock, nextver, :2, 1,''UPDATE'', version, delstatus, :4), delstatus=delstatus+1 where rowid = :1 ; end;' ;

            EXECUTE immediate upd_stmt USING :OLD.WM_ROWID,

            l_state,

            cur_time,

            isRef,

            N$FEATURE,

            N$ROAD_PIECE_ID,

            N$ROAD_ID,

            N$CREATED_DATE,

            N$MODIFIED_DATE,

            N$CREATED_USERID,

            N$MODIFIED_USERID,

            N$TRANSPORTATION_PIECE_ID,

            N$RANGE_SOURCE_CODE,

            N$ADDRESS_INTERP_CODE,

            N$RANGE_ADDED_DATE,

            N$FROM_ADDRESS_LEFT,

            N$FROM_ADDRESS_RIGHT,

            N$TO_ADDRESS_LEFT,

            N$TO_ADDRESS_RIGHT,

            N$ZIPCODE_LEFT,

            N$ZIPCODE_RIGHT,

            N$MALI_RD_SEG_RD_PIECE_ID,

            N$ADDRESS_LEFT_PREFIX,

            N$ADDRESS_RIGHT_PREFIX,

            N$NATIONAL_FOREST_ID,

            N$BOATING_ACCESS_SITE_CODE,

            N$STATE_PARK_CODE,

            N$BIA_OWNERSHIP_CLASS_CODE,

            N$NHS_CODE,

            N$NFC_CODE,

            N$LEGAL_SYSTEM_CODE,

            N$SINTMATCH,

            N$SRATE,

            N$SDIR,

            N$FACILITY_TYPE_ID,

            N$CROSSOVER_ID;

          ELSE

            IF (:old.WM_version = curver OR NVL(l_state, 'LIVE') = 'LIVE' OR instr(wmsys.lt_ctx_pkg.versInCurWspc, ',' || :old.WM_version || ',') > 0) THEN

              /* The row is in our ancestor(s) and in the current state */

              UPDATE MGFEE.ROAD_PIECE_LT

              SET retireTime = cur_time,

                ltlock       = wmsys.lt_ctx_pkg.checkngetlock(lockmode, ltlock, nextver, l_state, 0,'UPDATE', version, delstatus, isRef),

                nextver      = wmsys.lt_ctx_pkg.getNextVer(nextver,curver,version,l_state,crStatus,762)

              WHERE ROWID    = :old.WM_rowid;

            ELSE

              /* The row is in our ancestor(s) and not in the current state */

              UPDATE MGFEE.ROAD_PIECE_LT

              SET ltlock  = wmsys.lt_ctx_pkg.checkngetlock(lockmode, ltlock, nextver, l_state, 0,'UPDATE', version, delstatus, isRef),

                nextver   = wmsys.lt_ctx_pkg.getNextVer(nextver,curver,version,l_state,crStatus,762)

              WHERE ROWID = :old.WM_rowid;

            END IF;

            /* If lockmode is set, then generate the current lock info, * else carry-forward the parent lock to current version. */

            IF (:old.FEATURE IS NOT NULL AND :new.FEATURE IS NOT NULL) THEN

              SELECT rowid bulk collect

              INTO rids

              FROM MGFEE.MI_RELATION$

              WHERE tg_layer_id IN

                (SELECT tg_layer_id

                FROM user_sdo_topo_metadata

                WHERE topology_id  = :new.FEATURE.topology_id

                AND child_layer_id = ABS(:new.FEATURE.tg_layer_id)

                )

              AND topo_type   = :old.FEATURE.tg_id ;

              IF (rids.first IS NOT NULL) THEN

                forall wm_indx IN rids.first..rids.last

                UPDATE MGFEE.MI_RELATION$_BASE

                SET topo_type  = :new.FEATURE.tg_id

                WHERE wm_rowid = rids(wm_indx) ;

              END IF ;

              SELECT rowid bulk collect

              INTO rids

              FROM MGFEE.MI_RELATION$

              WHERE (tg_layer_id, tg_id, topo_id, topo_type) IN

                (SELECT tg_layer_id,

                  :old.FEATURE.tg_id,

                  topo_id,

                  topo_type

                FROM MDSYS.SDO_TOPO_RELATION_DATA

                WHERE tg_layer_id  = :new.FEATURE.tg_layer_id

                AND tg_id          = :new.FEATURE.tg_id

                AND topo_attribute = 'D'

                ) ;

              IF (rids.first IS NOT NULL) THEN

                forall wm_indx IN rids.first..rids.last

                DELETE MGFEE.MI_RELATION$_BASE WHERE wm_rowid = rids(wm_indx) ;

              END IF ;

              IF (:old.FEATURE.tg_id != :new.FEATURE.tg_id) THEN

                SELECT COUNT(*)

                INTO dummy

                FROM mdsys.sdo_topo_relation_data

                WHERE tg_layer_id  = :new.FEATURE.tg_layer_id

                AND tg_id          = :new.FEATURE.tg_id

                AND topo_attribute = 'A' ;

                IF (dummy          >0) THEN

                  INSERT

                  INTO MGFEE.MI_RELATION$

                    (SELECT tg_layer_id,

                        :new.FEATURE.tg_id,

                        topo_id,

                        topo_type,

                        topo_attribute

                      FROM MGFEE.MI_RELATION$

                      WHERE tg_layer_id = :old.FEATURE.tg_layer_id

                      AND tg_id         = :old.FEATURE.tg_id

                    ) ;

                END IF ;

                IF (wmsys.lt_ctx_pkg.dml_var = 'ON') THEN

                  SELECT COUNT(*)

                  INTO dummy

                  FROM mdsys.sdo_topo_relation_data

                  WHERE tg_layer_id = :new.FEATURE.tg_layer_id

                  AND tg_id         = :new.FEATURE.tg_id ;

                  IF (dummy         =0) THEN

                    mdsys.mderr.raise_md_error('MD', 'SDO', -13122);

                  END IF ;

                END IF ;

              END IF ;

              DELETE

              FROM mdsys.sdo_topo_relation_data

              WHERE tg_layer_id   = :new.FEATURE.tg_layer_id

              AND tg_id           = :new.FEATURE.tg_id

              AND topo_attribute IN ('A', 'D');

            END IF ;

            IF (:old.FEATURE IS NULL AND :new.FEATURE IS NOT NULL) THEN

              DELETE

              FROM mdsys.sdo_topo_relation_data

              WHERE tg_layer_id   = :new.FEATURE.tg_layer_id

              AND tg_id           = :new.FEATURE.tg_id

              AND topo_attribute IN ('A', 'D');

            END IF ;

            IF (:old.FEATURE   IS NOT NULL) THEN

              IF (:new.FEATURE IS NULL OR :old.FEATURE.tg_id != :new.FEATURE.tg_id) THEN

                DELETE

                FROM MGFEE.MI_RELATION$ topRt

                WHERE topRt.tg_layer_id = :old.FEATURE.tg_layer_id

                AND topRt.tg_id         = :old.FEATURE.tg_id;

              END IF ;

              DELETE MGFEE.MDTP_2F4B8$ WHERE sdo_tg_id = :old.FEATURE.tg_id;

            END IF;

            /* Insert the changed row in our version. */

            INSERT

            INTO MGFEE.ROAD_PIECE_LT

              (

                FEATURE,

                ROAD_PIECE_ID,

                ROAD_ID,

                CREATED_DATE,

                MODIFIED_DATE,

                CREATED_USERID,

                MODIFIED_USERID,

                TRANSPORTATION_PIECE_ID,

                RANGE_SOURCE_CODE,

                ADDRESS_INTERP_CODE,

                RANGE_ADDED_DATE,

                FROM_ADDRESS_LEFT,

                FROM_ADDRESS_RIGHT,

                TO_ADDRESS_LEFT,

                TO_ADDRESS_RIGHT,

                ZIPCODE_LEFT,

                ZIPCODE_RIGHT,

                MALI_RD_SEG_RD_PIECE_ID,

                ADDRESS_LEFT_PREFIX,

                ADDRESS_RIGHT_PREFIX,

                NATIONAL_FOREST_ID,

                BOATING_ACCESS_SITE_CODE,

                STATE_PARK_CODE,

                BIA_OWNERSHIP_CLASS_CODE,

                NHS_CODE,

                NFC_CODE,

                LEGAL_SYSTEM_CODE,

                SINTMATCH,

                SRATE,

                SDIR,

                FACILITY_TYPE_ID,

                CROSSOVER_ID,

                VERSION,

                CREATETIME,

                RETIRETIME,

                NEXTVER,

                DELSTATUS,

                LTLOCK

              )

              VALUES

              (

                N$FEATURE,

                N$ROAD_PIECE_ID,

                N$ROAD_ID,

                N$CREATED_DATE,

                N$MODIFIED_DATE,

                N$CREATED_USERID,

                N$MODIFIED_USERID,

                N$TRANSPORTATION_PIECE_ID,

                N$RANGE_SOURCE_CODE,

                N$ADDRESS_INTERP_CODE,

                N$RANGE_ADDED_DATE,

                N$FROM_ADDRESS_LEFT,

                N$FROM_ADDRESS_RIGHT,

                N$TO_ADDRESS_LEFT,

                N$TO_ADDRESS_RIGHT,

                N$ZIPCODE_LEFT,

                N$ZIPCODE_RIGHT,

                N$MALI_RD_SEG_RD_PIECE_ID,

                N$ADDRESS_LEFT_PREFIX,

                N$ADDRESS_RIGHT_PREFIX,

                N$NATIONAL_FOREST_ID,

                N$BOATING_ACCESS_SITE_CODE,

                N$STATE_PARK_CODE,

                N$BIA_OWNERSHIP_CLASS_CODE,

                N$NHS_CODE,

                N$NFC_CODE,

                N$LEGAL_SYSTEM_CODE,

                N$SINTMATCH,

                N$SRATE,

                N$SDIR,

                N$FACILITY_TYPE_ID,

                N$CROSSOVER_ID,

                curver,

                cur_time,

                NULL,

                wmsys.lt_ctx_pkg.CRCurNextver,

                :old.WM_delstatus+1,

                wmsys.lt_ctx_pkg.cur_lock_str

              )

            RETURNING rowid

            INTO l_rowid2;

          END IF;

          wmsys.lt_ctx_pkg.update_modified_tables('MGFEE','ROAD_PIECE', curver, l_state,762) ;

          wmsys.lt_ctx_pkg.wm$hasCRChild := prev_wm$hasCRChild;

          wmsys.lt_ctx_pkg.old_nextver   := prev_old_nextver;

          wmsys.lt_ctx_pkg.op_ctx        := prev_op_ctx;

        EXCEPTION

        WHEN OTHERS THEN

          wmsys.lt_ctx_pkg.wm$hasCRChild := prev_wm$hasCRChild;

          wmsys.lt_ctx_pkg.old_nextver   := prev_old_nextver;

          wmsys.lt_ctx_pkg.op_ctx        := prev_op_ctx;

          IF (lock_status                 = 0) THEN

            wmsys.lt_ctx_pkg.releaseLock(wmsys.lt_ctx_pkg.state_lock_id);

          END IF;

          RAISE;

        END;

      END;

        • 1. Re: ORA-20092: cannot UPDATE because locking is on and row is already versioned
          Ben Speckhard-Oracle

          Hi,

           

          This typically occurs when, after creating a workspace, the parent workspace updates the row.  Then, when the child workspace tries to lock it, it is unable to do so due to the row already being versioned(updated) in the parent.  While the row might not be locked in the parent, it would prevent another workspace from obtaining a lock on it.


          If this is the case, your options to resolve this are:

          (1) Refresh the child workspace.  The child workspace would then be able to view the latest version of the row and lock it as necessary.

          (2) Rollback the change to the row in the parent workspace.

          (3) Update the row in the child workspace in such a way that a persistent lock does not need to be applied.


          If the above does not describe your situation, that I would need more details about when and in which workspaces the row was modified.


          Regards,

          Ben