Database Software

Here is the situation. I have a parent table with child rows in LIVE. A workspace is then created, and another child is added to one of the parent rows in live. Another workspace is then created, and the parent row for the first workspaces child is deleted. The user tries to merge the second workspace to LIVE, which fails with a ORA-20237 error (expected).

How do I in an automated fashion, determine what row(s) in what workspace(s) are causing the error workspace merge? This is on 10gR2.

Integrity constraint violations do not show up in the CONF views, so that doesn’t help. I need to be able to eliminate the conflicts in an automated fashion, providing notice to the owners of the workspaces that they need to go revise the parts that were forcefully removed. But to do that, I need to know how to locate the issues causing the problem so I can build a package to do this, hands off.

Example script:

SQL> create table test_parent (pk number not null, name varchar2(50),
  2   CONSTRAINT test_parent_ndx PRIMARY KEY (pk));

Table created.

SQL> create table test_child (pk number not null,  fk number, name varchar2(
  2    CONSTRAINT test_child_ndx PRIMARY KEY (pk),
  3    CONSTRAINT test_child_FK01
  4   FOREIGN KEY (fk)
  5   REFERENCES test_parent (pk)
  6   on delete cascade);

Table created.

SQL> exec dbms_wm.enableversioning('test_parent,test_child');

PL/SQL procedure successfully completed.

SQL> insert into test_parent values (1, 'First item');

1 row created.

SQL> insert into test_parent values (2, 'Second item');

1 row created.

SQL> insert into test_child values (101, 1, 'References first item');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         1 First item
         2 Second item

SQL> select * from test_child;

        PK         FK NAME
---------- ---------- --------------------------------------------------
       101          1 References first item


SQL> exec DBMS_WM.createworkspace('1', TRUE, 'None',  TRUE );

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.gotoworkspace('1');

PL/SQL procedure successfully completed.

SQL> insert into test_child values (102, 1, 'References first item');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         1 First item
         2 Second item

SQL> select * from test_child;

        PK         FK NAME
---------- ---------- --------------------------------------------------
       101          1 References first item
       102          1 References first item

SQL> exec DBMS_WM.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.createworkspace('2', TRUE, 'None',  TRUE );

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.gotoworkspace('2');

PL/SQL procedure successfully completed.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         1 First item
         2 Second item

SQL> select * from test_child;

        PK         FK NAME
---------- ---------- --------------------------------------------------
       101          1 References first item

SQL> delete from test_parent where pk = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test_parent;

        PK NAME
---------- --------------------------------------------------
         2 Second item

SQL> select * from test_child;

no rows selected

SQL> exec DBMS_WM.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_WM.mergeworkspace('2', FALSE, FALSE, FALSE  );
BEGIN DBMS_WM.mergeworkspace('2', FALSE, FALSE, FALSE  ); END;

*
ERROR at line 1:
ORA-20237: integrity constraint (CVC.TEST_CHILD_FK01) violated in workspace
LIVE or one of its descendants - child record found
ORA-06512: at "WMSYS.LT", line 6009
ORA-06512: at line 1

Bryan