This discussion is archived
6 Replies Latest reply: Jan 18, 2012 12:39 AM by 907970 RSS

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

907970 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
  • 6. Re: Merge repeatedly, it will ignore the exception about ORA-20006
    907970 Newbie
    Currently Being Moderated
    Thanks a lot, Ben.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points