[Chapter 16] 16.3 Priority Groups with DBMS_REPCATChapter 16Conflict Resolution 16.3 Priority Groups with DBMS_REPCATPriority groups allow you to determine the validity of data based on its value. The priority group conflict resolution technique is most effective for data that has a finite range of possible values, and that goes through this range in a specific order. 16.3.1 About Priority GroupsConsider the products table:SQL>desc products Name Null? Type ------------------------------- -------- ---- PRODUCT_ID NOT NULL NUMBER(9) PRODUCT_TYPE NOT NULL NUMBER(6) CATALOG_ID NOT NULL VARCHAR2(15) DESCRIPTION NOT NULL VARCHAR2(30) REV_LEVEL NOT NULL VARCHAR2(15) PRODUCTION_DATE NOT NULL DATE PRODUCTION_STATUS NOT NULL VARCHAR2(12) AUDIT_DATE NOT NULL DATE AUDIT_USER NOT NULL VARCHAR2(30) GLOBAL_NAME NOT NULL VARCHAR2(20)The PRODUCTION_STATUS field in this table can only take on certain values: CONCEPT, DEVELOPMENT, BETA, PRODUCTION, and DISCONTINUED. In addition, products must go through this range of values in the order given.This concept of a sequential range of values is known as a workflow, and priority groups are designed to enforce the rules of a workflow in a replicated environment. Unlike column groups, which pertain to fields in a specific table, you can define a priority group for a specific column, which may appear in one or more tables. Once you define and configure a priority group, you can designate it to resolve update conflicts within a column group. The basic idea is that if a conflict arises, the row with the data corresponding to the higher priority in the workflow "wins."Use the following programs to create and maintain priority groups:DBMS_REPCAT.ADD_PRIORITY_<datatype>DBMS_REPCAT.ALTER_PRIORITYDBMS_REPCAT.ALTER_PRIORITY_<datatype>DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUPSDBMS_REPCAT.DEFINE_PRIORITY_GROUPSDBMS_REPCAT.DROP_PRIORITYDBMS_REPCAT.DROP_PRIORITY_GROUPDBMS_REPCAT.DROP_PRIORITY_<datatype>16.3.2 Creating, Maintaining, and Dropping Priority GroupsDBMS_REPCAT's DEFINE_PRIORITY_GROUP and DROP_PRIORITY_GROUP procedures allow you to create and drop priority groups. You use the COMMENT_ON_PRIORITY_GROUP procedure to maintain the comment on the priority group.16.3.2.1 The DBMS_REPCAT.DEFINE_PRIORITY_GROUP procedureThe DEFINE_PRIORITY_GROUP procedure creates a new priority group. The specifications differ for Oracle7 and Oracle8 as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.DEFINE_PRIORITY_GROUP (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, datatype IN VARCHAR2, fixed_length IN INTEGER := NULL, comment IN VARCHAR2 := NULL, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.DEFINE_PRIORITY_GROUP (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, datatype IN VARCHAR2, fixed_length IN INTEGER := NULL, comment IN VARCHAR2 := NULL);Parameters are summarized in the following table. NameDescriptiongnameName of the replication group containing the priority group.pgroupName of the priority group.datatypeDatatype for the value used in the priority group. Supported datatypes:CHARNCHAR (Oracle8 only)VARCHAR2NUMBERDATERAWfixed_lengthFixed length for values. Used only for datatype CHAR.commentComment.sname (Oracle7 only)Not used.16.3.2.1.1 ExceptionsThe DEFINE_PRIORITY_GROUP procedure may raise the following exceptions:NameNumberDescriptionduplicateprioritygroup-23335Priority group pgroup already existsmissingschema-23306Schema does not existnonmasterdef-23312Calling site is not the master definition sitetypefailure-23319Datatype not supported16.3.2.1.2 RestrictionsNote the following restrictions on calling DEFINE_PRIORITY_GROUP:You must call the DEFINE_PRIORITY_GROUP procedure from the master definition site.You must call GENERATE_REPLICATION_SUPPORT for any object in the replication group for the new priority group to become active.16.3.2.1.3 ExampleSince priority groups are meant to work with a specific range of values, you must specify the datatype of these values when you create the group. Valid datatypes follow:CHARNCHAR (Oracle8 only)VARCHAR2NUMBERDATERAWIf the data type is CHAR, then you must also specify the length of the data with the fixed_length parameter. After you create a priority group, you must run DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for any object in the same replication group to propagate the new priority group to other master sites. (Since priority groups are not associated with a specific object, it does not matter what object you use in the call the GENERATE_REPLICATION_SUPPORT.)16.3.2.1.4 Creating a priority group for datatype CHARThis call creates a priority group for a CHAR datatype. For the sake of this example, assume that the range of values is GREEN, YELLOW, RED, and the longest string is six characters long. BEGIN DBMS_REPCAT.DEFINE_PRIORITY_GROUP( gname => 'SPROCKET', pgroup => 'PG_SIGNAL_COLORS', datatype => 'CHAR', fixed_length => 6, comment => 'PG_SIGNAL_COLORS created '||sysdate); END;16.3.2.1.5 Creating a priority group for datatype VARCHARFor all other datatypes, the use of the fixed_length parameter does not apply. This statement creates a priority group for use with the PRODUCTION_STATUS field in the PRODUCTS table: BEGIN DBMS_REPCAT.DEFINE_PRIORITY_GROUP( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', datatype => 'VARCHAR', comment => 'PG_PRODUCTION_STATUS created '||sysdate); END;16.3.2.2 The DBMS_REPCAT.DROP_PRIORITY_GROUP procedureThe DROP_PRIORITY_GROUP procedure lets you drop a priority group that you have defined. The specifications differ for Oracle7 and Oracle8 as follows.here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.DROP_PRIORITY_GROUP (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.DROP_PRIORITY_GROUP (gname IN VARCHAR2 := '', pgroup IN VARCHAR2);Parameters are summarized in the following table.NameDescriptiongnameName of the replication group containing the priority grouppgroupName of the priority group to dropsname (Oracle7 only)Not usedWARNING: Do not drop a priority group that you have designated as an UPDATE conflict resolution method for a column group. You must first use DROP_UPDATE_RESOLUTION for the column group. Records in the data dictionary view DBA_REPRESOLUTION indicate if and where the priority group is used. Attempting to drop a priority group that is in use raises the referenced exception.16.3.2.2.1 ExceptionsThe DROP_PRIORITY_GROUP procedure may raise the following exceptions:NameNumberDescriptionmissingrepgroup-23373Replication group gname does not existnonmasterdef-23312Calling site is not the master definition sitereferenced-23332Priority group pgroup is used by existing conflict resolution methods16.3.2.2.2 RestrictionsYou must call DBMS_REPCAT.DROP_PRIORITY_GROUP from the master definition site.16.3.2.2.3 ExampleYou can use DBMS_REPCAT.DROP_PRIORITY_GROUP as follows to remove a particular priority group from the replication group:BEGIN DBMS_REPCAT.DROP_PRIORITY_GROUP( gname =>'SPROCKET', pgroup =>'PG_PRODUCTION_STATUS'); END;16.3.2.3 The DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP procedureThe COMMENT_ON_PRIORITY_GROUP procedure allows you to create or replace the comment for a priority group (as seen in the DBA_REPPRIORITY_GROUP data dictionary view). The specifications for Oracle7 and Oracle8 differ as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, comment IN VARCHAR2, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, comment IN VARCHAR2);Parameters are summarized in the following table.NameDescriptiongnameName of the replication group containing the priority grouppgroupName of the priority groupcommentCommentsname (Oracle7 only)Not used16.3.2.3.1 ExceptionsThe COMMENT_ON_PRIORITY_GROUP procedure may raise the following exceptions:NameNumberDescriptionmissingprioritygroup-23336Priority group pgroup does not existmissingrepgroup-23373Replication group gname does not existnonmasterdef-23312Calling site is not the master definition site16.3.2.3.2 RestrictionsYou must call COMMENT_ON_PRIORITY_GROUP from the master definition site.16.3.2.3.3 ExampleThe following illustrates how you can replace the comment for the PG_SIGNAL_COLORS priority group: BEGIN DBMS_REPCAT.COMMENT_ON_PRIORITY_GROUP( gname => 'SPROCKET', comment => 'Valid values are GREEN, YELLOW, and RED'); END;16.3.3 Creating and Maintaining Priorities Within a Priority GroupThe next step after creating a priority group is to add priorities to it. This task entails specifying every possible value for the data in the priority group, and assigning a priority to each value.For example, recall the PRODUCTION_STATUS field we described earlier, which has this range of five possible values:CONCEPTDEVELOPMENTBETAPRODUCTIONDISCONTINUEDWe want to resolve conflicts for this data by accepting the data that is furthest in the production cycle. If a conflict arises in which one update has PRODUCTION_STATUS set to "BETA," and another update has it set to "PRODUCTION," we would take the data from the latter update.The examples in the following sections illustrate exactly how to implement this priority group. We will show the following packages:DBMS_REPCAT.ADD_PRIORITY_<datatype>DBMS_REPCAT.ALTER_PRIORITYDBMS_REPCAT.ALTER_PRIORITY_<datatype>DBMS_REPCAT.DROP_PRIORITYDBMS_REPCAT.DROP_PRIORITY_<datatype>NOTE: Each of the procedures containing the <datatype> suffix actually has five different versions in Oracle7, one for each of the datatypes CHAR, VARCHAR2, NUMBER, RAW, and DATE. Oracle8 adds support for two more datatypes: NCHAR and NVARCHAR2. The usage of each of these packages is identical. Most of the examples in the following sections will use the VARCHAR2 version of these packages.16.3.3.1 The DBMS_REPCAT.ADD_PRIORITY_<datatype> procedureThe ADD_PRIORITY_<datatype> procedure adds a member (of the specified datatype) to an existing priority group. The specifications differ for Oracle7 and Oracle8 as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.ADD_PRIORITY_<datatype> (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, value IN {CHAR|VARCHAR2|NUMBER|DATE|RAW, priority IN NUMBER, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.ADD_PRIORITY_<datatype> (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, value IN {CHAR|NCHAR|VARCHAR2|NUMBER|DATE|RAW, priority IN NUMBER)In these specifications, <datatype> can be any of the following, and value can be any of these types:CHARVARCHAR2NUMBERDATERAWNCHAR (Oracle8 only)NVARCHAR2 (Oracle8 only)Parameters are summarized in the following table.NameDescriptiongnameName of the replication group to which priority group pgroup belongspgroupPriority group to which new value and priority are being addedvalueLiteral value that is being assigned added to pgrouppriorityPriority designated to valuesname (Oracle7 only)Not used16.3.3.1.1 ExceptionsThis procedure may raise the following exceptions:NameNumberDescriptionduplicatepriority-23335Another value is already designated with priority priorityduplicatevalue-23338Value is already in the priority group pgroupmissingprioritygroup-23336Priority group pgroup does not existmissingrepgroup-23373Replication group gname does not existnonmasterdef-23312Calling site is not the master definition sitetypefailure-23319Datatype of value is not the same as the datatype for priority group pgroup16.3.3.1.2 RestrictionsNote the following restrictions on calling ADD_PRIORITY_ <datatype>:The new value must be unique within the priority group. The new priority must be unique within the priority group.ADD_PRIORITY_<datatype> must be called from the master definition site.16.3.3.1.3 ExampleTo associate priorities with each of the five possible values of PRODUCTION_STATUS, we must make five calls to ADD_PRIORITY_VARCHAR2. After making these calls and a call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT, the column group is completely configured. BEGIN DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', value => 'CONCEPT', priority => 10); DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', value => 'DEVELOPMENT', priority=> 20); DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', value => 'BETA', priority=> 30); DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', value => 'PRODUCTION', priority=> 40); DBMS_REPCAT.ADD_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', value => 'DISCONTINUED', priority=> 50); END;TIP: It is a good idea to number priorities in multiples of 10 or more so that you can easily add new priority values later as requirements change.16.3.3.2 The DBMS_REPCAT.ALTER_PRIORITY procedureThe ALTER_PRIORITY procedure lets you change the priority associated with a specific value in a priority group. The specifications differ for Oracle7 and Oracle8 as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.ALTER_PRIORITY (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, old_priority IN NUMBER, new_priority IN NUMBER, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.ALTER_PRIORITY (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, old_priority IN NUMBER, new_priority IN NUMBER)Parameters are summarized in the following table.NameDescriptiongnameName of the replication group to which priority group pgroup belongspgroupName of the priority group whose priority is being alteredold_prioritypgroup's previous priority valuenew_prioritypgroup's new priority valuesnameNot used16.3.3.2.1 ExceptionsThe ALTER_PRIORITY procedure may raise the following exceptions:NameNumberDescriptionduplicatepriority-23335Priority new_priority already exists in priority group pgroupmissingprioritygroup-23336Priority group pgroup does not existmissingvalue-23337Value was not registered (with a call to ADD_PRIORITY_<datatype>nonmasterdef-23312Calling site is not the master definition site16.3.3.2.2 RestrictionsNote the following restrictions on calling ALTER_PRIORITY:You must call the ALTER_PRIORITY procedure from the master definition site.The new priority must be unique within the priority group.16.3.3.2.3 ExamplesSuppose that our requirements change such that we want the PRODUCTION_STATUS value DEVELOPMENT to have higher priority than BETA. We can accomplish this by changing the priority associated with DEVELOPMENT from 30 to 45. BEGIN DBMS_REPCAT.ALTER_PRIORITY( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', old_priority => 30, new_priority => 45); END;As with the call to ADD_PRIORITY_<datatype>, this change takes effect after the next call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT.16.3.3.3 The DBMS_REPCAT.ALTER_PRIORITY_<datatype> procedureThe ALTER_PRIORITY_<datatype> procedures let you alter the data value associated with a specific priority for a priority group. The specifications differ for Oracle7 and Oracle8 as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.ALTER_PRIORITY_<datatype> (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, old_value IN {CHAR|VARCHAR2|NUMBER|DATE|RAW}, new_value IN {CHAR|VARCHAR2|NUMBER|DATE|RAW}, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.ALTER_PRIORITY_<datatype> (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, old_value IN {CHAR|NCHAR|VARCHAR2|NUMBER|DATE|RAW}, new_value IN {CHAR|NCHAR|VARCHAR2|NUMBER|DATE|RAW});<datatype> can be one of the following, and value and old_value can be any of these types:CHARVARCHAR2NUMBERDATERAWNCHAR (Oracle8 only)NVARCHAR2 (Oracle8 only)Parameters are summarized in the following table.NameDescriptiongnameName of the replication group to which priority group pgroup belongspgroupName of the priority group whose priority is being alteredold_valueCurrent value of the priority group membernew_valueNew value of the priority group membersname (Oracle7 only)Not used16.3.3.3.1 ExceptionsThe ALTER_PRIORITY_<datatype> procedure may raise the following exceptions:NameNumberDescriptionduplicatevalue-23338Value new_value is already designated a priority in priority group pgroupmissingprioritygroup-23336Priority group pgroup does not existmissingvalue-23337Value was not registered (with a call to ADD_PRIORITY_<datatype>nonmasterdef-23312Calling site is not the master definition site16.3.3.3.2 RestrictionsNote the following restrictions on calling ALTER_PRIORITY_<datatype>:You must call the ALTER_PRIORITY_<datatype> procedure from the master definition site.The new priority must be unique within the priority group.16.3.3.3.3 ExampleSuppose that we want to change the data value associated with priority 50 from DISCONTINUED to OBSOLETE. We would make the following call: BEGIN DBMS_REPCAT.ALTER_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', old_value => 'DISCONTINUED', new_value => 'OBSOLETE'); END;This call would take effect after the next call to GENERATE_REPLICATION_SUPPORT for an object in the SPROCKET replication group.16.3.4 Dropping Priorities from a Priority GroupThe DROP_PRIORITY and DROP_PRIORITY_<datatype> remove values from a priority group. You can specify the value to be removed by priority (with DROP_PRIORITY) or by data value (with DROP_PRIORITY_<datatype>).16.3.4.1 The DBMS_REPCAT.DROP_PRIORITY procedureThe DROP_PRIORITY procedure removes a value from a priority group. In this version of the procedure, you must specify the value by priority. The specifications differ for Oracle7 and Oracle 8 as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.DROP_PRIORITY (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, priority_num IN NUMBER, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.DROP_PRIORITY (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, priority_num IN NUMBER);Parameters are summarized in the following table.NameDescriptiongnameName of the replication group to which priority group pgroup belongspgroupName of the priority group whose priority is being alteredpriority_numPriority for the value to be droppedsnameNot used16.3.4.1.1 ExceptionsThe DROP_PRIORITY procedure may raise the following exceptions:NameNumberDescriptionmissingprioritygroup-23336Priority group pgroup does not exist.missingrepgroup-23373Replication group gname does not exist.nonmasterdef-23312Calling site is not the master definition site.16.3.4.1.2 RestrictionsYou must call the DROP_PRIORITY procedure from the master definition site.16.3.4.1.3 ExampleIn the following example, we drop the member of the PG_PRODUCTION_STATUS priority group whose priority is 50: BEGIN DBMS_REPCAT.DROP_PRIORITY( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', priority => 50); END;This change takes effect the next time we run DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for an object in the SPROCKET replication group.16.3.4.2 The DBMS_REPCAT.DROP_PRIORITY_<datatype> procedureThe DROP_PRIORITY_<datatype> procedure removes a value from a priority group. In this version of the procedure, you can specify the value by data value. The specifications differ for Oracle7 and Oracle 8 as follows.Here is the Oracle7 specification:PROCEDURE DBMS_REPCAT.DROP_PRIORITY_<datatype> (gname IN VARCHAR2 := '', pgroup IN VARCHAR2, value IN {CHAR|VARCHAR2|NUMBER|DATE|RAW}, sname IN VARCHAR2 := '');Here is the Oracle8 specification:PROCEDURE DBMS_REPCAT.DROP_PRIORITY_<datatype> (name IN VARCHAR2 := '', pgroup IN VARCHAR2, value IN {CHAR|NCHAR|VARCHAR2|NUMBER|DATE|RAW}, sname IN VARCHAR2 := '');<datatype> can be any of the following, and value can be any of these types:CHARVARCHAR2NUMBERDATERAWNCHAR (Oracle8 only)NVARCHAR2 (Oracle8 only)Parameters are summarized in the following table. NameDescriptiongnameName of the replication group to which priority group pgroup belongspgroupPriority group to which new value and priority are being addedvalueLiteral value that is being assigned added to pgroupsname (Oracle7 only)Not used16.3.4.2.1 ExceptionsThe DROP_PRIORITY_<datatype> procedure may raise the following exceptions:NameNumberDescriptionmissingprioritygroup-23336Priority group pgroup does not existmissingrepgroup-23373Replication group gname does not existnonmasterdef-23312Calling site is not the master definition siteparamtype-23325Datatype of value is not the same as the datatype for priority group pgroup16.3.4.2.2 RestrictionsYou must call DROP_PRIORITY_<datatype> from the master definition site.16.3.4.2.3 ExampleYou can specify the member to be dropped by its data value rather than its priority (as was done with DROP_PRIORITY). In the following example, we drop the member of the PG_PRODUCTION_STATUS priority group whose value is CONCEPT: BEGIN DBMS_REPCAT.DROP_PRIORITY_VARCHAR2( gname => 'SPROCKET', pgroup => 'PG_PRODUCTION_STATUS', value => 'CONCEPT'); END;As with DROP_PRIORITY, this change takes effect after DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT has been run for any object in the replication group.16.2 Column Groups with DBMS_REPCAT16.4 Site Priority Groups with DBMS_REPCAT Copyright (c) 2000 O'Reilly & Associates. All rights reserved.