6 Replies Latest reply: Jan 18, 2012 2:39 AM by 907970 RSS

    Merge repeatedly, it will ignore the exception about ORA-20006

    907970
      Hi Ben,

      I confuse about merge implementation. When I merge one record, it will raise ORA-20006, the exception is reasonable because I delete its parent record in LIVE. However I merge repeatedly using anonymous block to implement, it merge successfully. And the loop times is different. Below is my test process:
      CREATE TABLE t_p
      (
      ID NUMBER PRIMARY KEY,
      t1_col VARCHAR2(10)
      );
      CREATE TABLE t_c
      (
      ID NUMBER PRIMARY KEY,
      p_id NUMBER REFERENCES t_p(ID),
      t2_col VARCHAR2(10)
      );
      INSERT INTO t_p VALUES(1101,'F1');
      INSERT INTO t_p VALUES(1102,'F2');

      INSERT INTO t_c VALUES(2101,1101,'C1');
      INSERT INTO t_c VALUES(2102,1102,'C1');

      2. Version these two tables.
      3. Mew a workspace named as 'w2'
      4. Delete F1 in t_p and its child record.
      DELETE FROM t_c WHERE ID = 2101;
      DELETE FROM t_p WHERE ID = 1101;
      5. Insert a child record for F1 in w1.
      INSERT INTO t_c VALUES(2103,1101,'C2');
      6. Use my anonymous block to merge w1

      SQL> DECLARE
      2 l_exit NUMBER := 0;
      3 BEGIN
      4 LOOP
      5 EXIT WHEN l_exit = 1;
      6 BEGIN
      7 dbms_wm.mergetable('w2','T_C','ID=2103',FALSE,FALSE,FALSE);
      8 l_exit := 1;
      9 EXCEPTION
      10 WHEN OTHERS THEN
      11 dbms_output.put_line(SQLERRM);
      12 END;
      13 END LOOP;
      14 END;
      15 /

      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found

      PL/SQL procedure successfully completed

      SQL>

      7. rollback the merge operation, invoke this anonymous block again, the exception has been raised different times.
      SQL> DECLARE
      2 l_exit NUMBER := 0;
      3 BEGIN
      4 LOOP
      5 EXIT WHEN l_exit = 1;
      6 BEGIN
      7 dbms_wm.mergetable('w2','T_C','ID=2103',FALSE,FALSE,FALSE);
      8 l_exit := 1;
      9 EXCEPTION
      10 WHEN OTHERS THEN
      11 dbms_output.put_line(SQLERRM);
      12 END;
      13 END LOOP;
      14 END;
      15 /

      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found
      ORA-20006: integrity constraint (TINA.SYS_C0085574) violated - parent key not found

      PL/SQL procedure successfully completed

      SQL>

      8. The inserted child record without parent record did in LIVE.
      SQL> select * from t_p;

      ID T1_COL
      ---------- ----------
      1102 F2

      SQL> select * from t_c;

      ID P_ID T2_COL
      ---------- ---------- ----------
      2102 1102 C1
      2103 1101 C2

      SQL>


      I think this record couldn't be merged into LIVE, but it did. I couldn't understand this situation well, is there something wrong?

      Thanks

      Tina

      Edited by: 904967 on 2011-12-29 上午1:46
        • 1. Re: Merge repeatedly, it will ignore the exception about ORA-20006
          Ben Speckhard-Oracle
          Hi Tina,

          I wasn't able to reproduce the behavior you are describing. Could you include the exact CreateWorkspace/GotoWorkspace procedural calls in your example. Also, what version of Workspace Manager are you using?

          Thanks,
          Ben
          • 2. Re: Merge repeatedly, it will ignore the exception about ORA-20006
            907970
            Hi Ben,

            Sorry for my late replay.
            My workspace is 11.2.0.1.0 version. I supplement the exact step below.

            1. Create table t_p, t_c as above.

            2.Version these two tables.

            BEGIN
            dbms_wm.enableversioning('T_C,T_P','VIEW_W_OVERWRITE');
            END;

            3. New a workspace named as 'w2'

            BEGIN
            dbms_wm.createworkspace(workspace => 'w2',isrefreshed =>FALSE,auto_commit => FALSE);
            END;

            4. Delete F1 in t_p and its child record. Currently, these operation are also in LIVE workspace.

            5. Go to workspace w2, insert a child record for F1 in w2 as above.
            BEGIN
            dbms_wm.gotoworkspace('w2');
            END;

            6. Use my anonymous block(also as above ) to merge w2.


            Best regards,
            Tina
            • 3. Re: Merge repeatedly, it will ignore the exception about ORA-20006
              Ben Speckhard-Oracle
              Hi Tina,

              I was now able to reproduce this. Thanks. This is more a result of how Oracle handles exception blocks.

              begin
              dbms_wm.mergetable('w2','T_C','ID=2103',FALSE,FALSE,FALSE);

              exception when others then
              dbms_output.put_line(SQLERRM);
              end ;

              Since the above block does not rollback or raise the error within the exception block, none of the partial work performed by the MergeTable procedure is rolled back as would normally be done when an exception is raised. As a result, the starting state of the data changes with each iteration, and it eventually successfully completes in a fashion that is invalid containing a row in the child table without a corresponding parent table row.

              However, I am unsure without further evaluation as to why it requires a different number of iterations to complete. Regardless, you would want to issue a rollback in the exception block. This would cause the procedure/loop to never complete, as should be the case.

              Regards,
              Ben
              • 4. Re: Merge repeatedly, it will ignore the exception about ORA-20006
                907970
                Hi Ben,

                Thanks for your reply. Add rollback in my exception block is a perfect measure. Handling exception errors, it's better to terminate the block and rollback to keep transaction consistency.
                However, I want to ignore some errors when I merge table, eg, ORA-20006, ORA-20005. Because I would merge table after collecting all of difference from a set of versioned tables. The order or merge is unknown. Such as the new child record merge firstly, this exception would be raised, I want to do nothing and continue to merge other record. After the new parent record has been merged, the child record would be merged successfully finally. I wonder whether this odd occurrence is the defect or not.

                Best Regards
                Tina
                • 5. Re: Merge repeatedly, it will ignore the exception about ORA-20006
                  Ben Speckhard-Oracle
                  Hi,

                  The ORA-20005, ORA-20006 errors are only raised if the result of all of the tables being merged results in a constraint violation. The check is not done after each table, which would be working with incomplete data. They are not errors that can be ignored.

                  Regards,
                  Ben