2 Replies Latest reply on Aug 26, 2014 7:15 AM by aschilling

    Oracle11gR2 Workspace Manager and table consistency after merge

    Luis A. Paolini

      Hi folks,

       

           I'm working with Oracle Workspace Manager in order to get data inserted and validated into workpaces before they become available to the LIVE workspace.

           Doing some tests I found a problem about data consistency after I merge the data from a child workspace to the parent workspace.

           To be able to explain and reproduce the problem I create a simple test case:

       

      ---------------------------------------------------------------------------------------------------------------------------------

      --Create table TB_LINK

      create table TB_LINK

      (

        CD_LINK NUMBER not null,

        DS_LINK VARCHAR2(30)

      );

       

      --Create primary key

      alter table TB_LINK add primary key (CD_LINK);

      ---------------------------------------------------------------------------------------------------------------------------------

      --Create table TB_GUD

      create table TB_GUD

      (

        CD_GUD  NUMBER not null,

        DS_GUD  VARCHAR2(30),

        CD_LINK NUMBER

      );

       

      -- Create primary key

      alter table TB_GUD add primary key (CD_GUD);

       

      -- Create foreign key 

      alter table TB_GUD

        add constraint FK_TB_LINK foreign key (CD_LINK)

        references TB_LINK (CD_LINK);

       

      ----------------------------------------------------------------------------------------------------------------------

      -- Create sequences

      create sequence SEQ_TB_GUD

      minvalue 1

      maxvalue 9999999999999999999999999999

      start with 1

      increment by 1

      nocache;

       

      create sequence SEQ_TB_LINK

      minvalue 1

      maxvalue 9999999999999999999999999999

      start with 1

      increment by 1

      nocache; 

      ----------------------------------------------------------------------------------------------------------------------------

      --Create Triggers

      create or replace trigger "INS_TB_GUD" before insert on TB_GUD for each row

      Begin

      select SEQ_TB_GUD.nextval into :new.CD_GUD from dual;

      end;

      /

       

      create or replace trigger "INS_TB_LINK" before insert on TB_LINK for each row

      Begin

      select SEQ_TB_LINK.nextval into :new.CD_LINK from dual;

      end;

      /

      ------------------------------------------------------------------------ 

      --Enable version TB_LINK and TB_GUD

      EXECUTE DBMS_WM.EnableVersioning('TB_GUD','VIEW_WO_OVERWRITE',FALSE,FALSE,'UNLIMITED');

      EXECUTE DBMS_WM.EnableVersioning('TB_LINK','VIEW_WO_OVERWRITE',FALSE,FALSE,'UNLIMITED');

       

      --Create a workspace

      EXECUTE DBMS_WM.CreateWorkspace ('TEST_WKS');

       

      --Goto workspace TEST_WKS

      EXECUTE dbms_wm.gotoworkspace('TEST_WKS');

       

      -------------------------------------------------------------------------

      --Insert data into TB_LINK and TB_GUD

      INSERT INTO TB_LINK(DS_LINK) VALUES ('DS1');

      INSERT INTO TB_LINK(DS_LINK) VALUES ('DS2');

      INSERT INTO TB_LINK(DS_LINK) VALUES ('DS3');

      INSERT INTO TB_LINK(DS_LINK) VALUES ('DS4');

      COMMIT;

       

      INSERT INTO TB_GUD(DS_GUD,CD_LINK) VALUES ('GUD1',1);

      INSERT INTO TB_GUD(DS_GUD,CD_LINK) VALUES ('GUD2',2);

      INSERT INTO TB_GUD(DS_GUD,CD_LINK) VALUES ('GUD3',3);

      INSERT INTO TB_GUD(DS_GUD,CD_LINK) VALUES ('GUD4',4);

      COMMIT;

      ----------------------------------------------------------------------------------------------------------------------

      --Checking keys

      select * from tb_link;

       

         CD_LINK      DS_LINK

           ----------       ------------------------------

               1           DS1

               2           DS2

               3           DS3

               4           DS4

       

      select * from tb_gud;

       

         CD_GUD      DS_GUD     CD_LINK

            ----------      --------------     ----------

               1           GUD1              1

               2           GUD2              2

               3           GUD3              3

               4           GUD4              4

       

      --------------------------------------------------------------------------------------------------------------------

      --Merge Workspace

      EXECUTE DBMS_WM.MergeWorkspace ('TEST_WKS');

       

      -------------------------------------------------------------------------

      --Checking keys

      EXECUTE dbms_wm.gotoworkspace('LIVE');

       

      select * from tb_link;


         CD_LINK      DS_LINK

            ----------      ------------------------------

               5           DS4

               6           DS3

               7           DS1

               8           DS2

       

      We can see that the CD_LINK got new values after merge and that was not expected.

       

      select * from tb_gud;

       

         CD_GUD      DS_GUD                  CD_LINK

            ----------      ------------------------------ ----------

               6           GUD3                       3

               7           GUD1                       1

               8           GUD2                       2

               5           GUD4                       4

       

       

      We can see that the CD_GUD got new values after merge and that was not expected.

      Now, the values for the CD_LINK column does not have corresponding records at the TB_LINK table, as the foreign key does not exist anymore.



      Could you please help me understand what is going on?

      Thanks,


      Luis



        • 1. Re: Oracle11gR2 Workspace Manager and table consistency after merge
          Ben Speckhard-Oracle

          Hi Luis,


          The reason for the difference is that the trigger is being run during the MergeWorkspace operation.  The inserts into the child workspace(TEST_WKS) translates into an insert into the LIVE workspace during merge as the rows do not yet exist.  As a result, the trigger is fired and the sequence is evaluated.  Ideally, we should not allow the PK to be modified by a sequence in this case.

          You have 2 options:
          (1) Check for :new.CD_GUD being null prior to using the sequence.  Any dml coming from a merge/refresh operation will have a non-null value.
          (2) Turn off the trigger during dbms_wm procedures.  This can be done using dbms_wm.SetTriggerEvents.  I would assume you would only want this trigger being run for DML events.

          Let me know if you have any questions.

          Regards,
          Ben

          • 2. Re: Oracle11gR2 Workspace Manager and table consistency after merge
            aschilling

            Hi Luis,

             

            to give some input as well: we use the option (1) Ben suggested: check whether the row really needs a new value from the sequence.

            This works absolutely fine with OWM.

            I can drop you some lines of PL/SQL if you like, which we use to generate the triggers in a generic way for our tables. No big secrets hidden in there, just making life easier for us :-)

             

            regards,

             

              Andreas