Problem: Custom conflict resoluton on oracle replication

From: <pavel1704_at_my-dejanews.com>
Date: Tue, 23 Mar 1999 10:24:15 GMT
Message-ID: <7d7q48$hke$1@nnrp1.dejanews.com>

I have a problem, defininig my own procedure for for conflict resolution, using oracle 7.3.3.0 replication. I have to sites - pavel with database “pavel” and kenan with database “oracle” On ‘kenan’ I have tables “ITEM” and “ITEM_PROJECT”: I have defined as recommended on guide of replication - db_link on ‘kenan’: CREATE PUBLIC DATABASE LINK PAVEL.WORLD CONNECT TO MATAN48 IDENTIFIED BY MATAN48 USING 'pavel'

And on ‘pavel’ :
CREATE PUBLIC DATABASE LINK ORACLE.WORLD CONNECT TO SYS
IDENTIFIED BY SYS
USING 'kenan'

On ‘kenan’ - I have created master group - “FOR_PAVEL” , consisting of tables - ‘ITEM’ and ‘ITEM_PROJECT’: Tables “ITEM” and ITEM_PROJECT have snapshot_logs:

CREATE SNAPSHOT LOG ON MATAN48.ITEM;
CREATE SNAPSHOT LOG ON MATAN48.ITEM_PROJECT; On side of ‘pavel’ - I have created snapshot group: “FOR_PAVEL” , including updatable snapshots on these two tables, replicated from ‘kenan’: And this replication scheme worked quite fine - that’s mean it really replicate data. Now I am trying to define my own routing if conflict resolution. Table “ITEM” has unique index - ITEM_IDX. I have defined routine, to log conflicts on this index. I have run on kenan: Connect matan48/bpdm; /*matan48 was granted as replication administrator on ‘kenan’*

I suspended replication activity on ‘kenan’ from “Replication manager” on master group “FOR_PAVEL”

SQLWKS> BEGIN
     2> DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
     3> sname=>'matan48',
     4> oname=>'ITEM',
     5> constraint_name =>'ITEM_IDX',
     6> sequence_no=>0,
     7> method=>'USER FUNCTION',
     8> parameter_column_name=>'item_key,revision,zz_id,db_zz_id',
     9> function_name=>'MATAN48.KEY_REV_CONFLICT');
    10> end;
    11>

Statement processed.

SQLWKS> BEGIN
     2> DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE('matan48','ITEM');
     3> end;
     4>

Statement processed.

Here is text of 'MATAN48.KEY_REV_CONFLICT': (‘kenan’ site) CREATE OR REPLACE FUNCTION MATAN48.KEY_REV_CONFLICT (    item_key IN OUT CHAR,
   revision IN OUT CHAR,
   zz_id IN OUT NUMBER,
   db_zz_id IN OUT NUMBER,
   ignore_discard_flag OUT BOOLEAN
   )

• Enter the parameters for the function in
• the brackets above. If this function has
• no parameters then delete the line
• ***************************************************
• * *
• * Author *
• * Creation Date *
• * Comments *
• * *
• *************************************************** RETURN BOOLEAN IS -- Enter all variables cursors and constants following -- this line BEGIN
• Enter the code for the function following
• this line INSERT INTO matan48.duplicate_items VALUES ('1', '1', 1, 1); COMMIT; select '2' INTO item_key FROM dual; revision := '2'; zz_id := 2; db_zz_id := 2; INSERT INTO matan48.duplicate_items VALUES (item_key, revision, zz_id, db_zz_id); COMMIT; ignore_discard_flag := TRUE; RETURN TRUE; EXCEPTION
• Enter the code to handle exception conditions
• following this line WHEN OTHERS THEN NULL; END;

After this I resumed replication activity , inserted on ‘pavel’ item, having same index values as record already existing on kenan. When I try to refresh - I Receive error on uniquenese conflict, but....

Nothing is inserted into “duplicate_items”…….

????????????????????????????????????????????

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Received on Tue Mar 23 1999 - 04:24:15 CST