[Chapter 16] 16.2 Column Groups with DBMS_REPCATChapter 16Conflict Resolution 16.2 Column Groups with DBMS_REPCATColumn groups provide a mechanism for guaranteeing data consistency across one or more columns in a replicated table. Every replicated table has at least one column group, called the default column group, which Oracle creates automatically. You can also create your own column groups (and you probably should) in which you group logically related fields.16.2.1 About Column GroupsSuppose that you have a customer table that contains address information, such as street address, city, state, and postal code, plus personal information such as last name, marital status, birth date, and phone number. SQL> desc customer Name Null? Type --------------- -------- ------ CUSTOMER_ID NUMBER(6) NAME VARCHAR2(30) MARITAL_STATUS VARCHAR2(1) PHONE_NUMBER VARCHAR2(16) STREET_ADDR VARCHAR2(30) CITY VARCHAR2(30) STATE VARCHAR2(30) POSTAL_CODE VARCHAR2(12) TIMESTAMP DATE GLOBAL_NAME VARCHAR2(30)In this table, the fields pertaining to the customer's address (i.e., STREET_ADDR, CITY, STATE, and POSTAL_CODE) are logically related. You would not want to allow an update at one site to set the CITY to "San Francisco" and an update at another site to set the STATE to "Mississippi" since (as of this writing) there is no such municipality as San Francisco, Mississippi.Oracle's answer to this potential catastrophe is the column group. A column group is a logical grouping of columns whose collective values are treated as a unit. If we create a column group and add the address-related fields STREET_ADDR, CITY, STATE, and POSTAL_CODE, we can be sure that rows in this table will always contain consistent values for these columns. We can also make a second column group consisting of the fields NAME, MARITAL_STATUS, and PHONE_NUMBER. Note that a row in this table could contain address information that was entered at one site, and name information that was entered at another site.As we shall see in the later section "Built-in Resolution Techniques," every column group needs to have a "governing" column that determines which data is to be considered correct. For example, if you want to use the Latest Timestamp resolution method for a given column group, then your table should include a DATE field, and your application should update this field with the current time whenever it performs inserts or updates on the table.NOTE: Oracle automatically creates a default column group, called the shadow column group, when you generate replication support for a table. This column group contains every field that you do not explicitly place in a column group of your own.The procedures you'll use to create and maintain column groups follow:DBMS_REPCAT.ADD_GROUPED_COLUMNDBMS_REPCAT.COMMENT_ON_COLUMN_GROUPDBMS_REPCAT.DEFINE_COLUMN_GROUPDBMS_REPCAT.DROP_COLUMN_GROUPDBMS_REPCAT.DROP_GROUPED_COLUMNDBMS_REPCAT.MAKE_COLUMN_GROUP16.2.2 Creating and Dropping Column GroupsThe DEFINE_COLUMN_GROUP, DROP_COLUMN_GROUP, and MAKE_COLUMN_GROUP procedures are used to create and drop column groups. The difference between DEFINE_COLUMN_GROUP and MAKE_COLUMN_GROUP is that the former creates a column group with no member columns, and the latter both creates the group and adds columns to it.16.2.2.1 The DBMS_REPCAT.DEFINE_COLUMN_GROUP procedureThe DEFINE_COLUMN_GROUP procedure creates a column group with no member columns. Here's the specification:PROCEDURE DBMS_REPCAT.DEFINE_COLUMN_GROUP (sname IN VARCHAR2, oname IN VARCHAR2, column_group IN VARCHAR2, comment IN VARCHAR@ := NULL);Parameters are summarized in the following table.NameDescriptionsnameName of the schema to which the replicated table belongsonameName of the replicated table containing the column groupcolumn_groupName of the column groupcommentComment16.2.2.1.1 ExceptionsThe DEFINE_COLUMN_GROUP procedure may raise the following exceptions:NameNumberDescriptionduplicategroup-23330Column_group already existsmissingobject-23308Object oname does not existnonmasterdef-23312Calling site is not master definition site16.2.2.1.2 RestrictionsNote the following restrictions on calling the DEFINE_COLUMN_GROUP:You must call this procedure from the quiesced master definition site.You must regenerate replication support for the table after defining the column group with the GENERATE_REPLICATION_SUPPORT procedure.16.2.2.1.3 ExampleThe DEEFINE_COLUMN_GROUP creates an empty column group -- that is, one with no members. After creating the column group, you can add columns to it with the DBMS_REPCAT.ADD_GROUPED_COLUMN procedure described later in this chapter. The following example creates an empty column group for table SPROCKET.PRODUCTS: BEGIN DBMS_REPCAT.DEFINE_COLUMN_GROUP(sname=> 'SPROCKET', oname => 'PRODUCTS ', column_group => 'CG_PRODUCTS_PRICE_COLS', comment => 'Comment added on '||sysdate|| ' by ' ||user); END;16.2.2.2 The DBMS_REPCAT.DROP_COLUMN_GROUP procedureThe DROP_COLUMN_GROUP procedure drops a column group that you've previously created. Here's the specification:PROCEDURE DBMS_REPCAT.DROP_COLUMN_GROUP (sname IN VARCHAR2, oname IN VARCHAR2, column_group IN VARCHAR2);Parameters are summarized in the following table.NameDescriptionsnameName of the schema to which the replicated table belongsonameName of the replicated table containing the column groupcolumn_groupName of the column group16.2.2.2.1 ExceptionsThe DROP_COLUMN_GROUP procedure may raise the following exceptions:NameNumberDescriptionmissinggroup-23331The column_group does not existmissingobject-23308The object oname does not existmissingschema-23306The schema sname does not existnonmasterdef-23312Calling site is not master definition sitereferenced-23332The column_group is used by existing conflict resolution methods16.2.2.2.2 RestrictionsNote the following restrictions on calling DROP_COLUMN_GROUP:You must call this procedure from the quiesced master definition site.You must regenerate replication support for the table after defining the column group with the GENERATE_REPLICATION_SUPPORT procedure.16.2.2.2.3 ExampleThis example drops the column group CG_PRODUCTS_PRICE_COLS that was created in the CREATE_COLUMN_GROUP example:BEGIN DBMS_REPCAT.DROP_COLUMN_GROUP(sname > 'SPROCKET', oname => 'PRODUCTS ', column_group => 'CG_PRODUCTS_PRICE_COLS'); END;16.2.2.3 The DBMS_REPCAT.MAKE_COLUMN_GROUP procedureThe MAKE_COLUMN_GROUP procedure creates a column group and adds member columns to it. Here's the specification:PROCEDURE DBMS_REPCAT.MAKE_COLUMN_GROUP (sname IN VARCHAR2, oname IN VARCHAR2, column_group IN VARCHAR2, {list_of_column_names IN VARCHAR2 | list_of_column_names IN dbms_repcat.varchar2s} );Note that you must specify only one of the list_of_column_names parameters.Parameters are summarized in the following table.NameDescriptionsnameName of the schema to which the replicated table belongs.onameName of the replicated table containing the column group.column_groupName of the column group.list_of_column_namesA comma-delimited list of column names, or a PL/SQL table of column names. Use `*' to add all columns in the table.16.2.2.3.1 ExceptionsThe MAKE_COLUMN_GROUP procedure may raise the following exceptions:NameNumberDescriptionduplicatecolumn-23333Column(s) already a member of a different column groupduplicategroup-23330column_group already existsmissingcolumn-23334Column(s) specified do not exist in table onamemissingobject-23308Object oname does not existnonmasterdef-23312Calling site is not master definition site16.2.2.3.2 RestrictionsNote the following restrictions on calling MAKE_COLUMN_GROUP:You must call this procedure from the quiesced master definition site.You must regenerate replication support for the table after defining the column group with the DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT procedure.16.2.2.3.3 ExampleBy passing "*" to the list_of_column_names parameter in MAKE_COLUMN_GROUP, you can create a column group consisting of all columns in the table. BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP(sname=> 'SPROCKET', oname => 'PRODUCTS ', column_group => 'CG_PRODUCTS_ALL_COLS', list_of_column_names => '*'); END;You can also use MAKE_COLUMN_GROUP to create a column group containing whatever subset of columns you want. BEGIN DBMS_REPCAT.MAKE_COLUMN_GROUP( sname => 'SPROCKET', oname => 'PRODUCTS ', column_group => 'CG_PRODUCTS_MFG_COLS', list_of_column_names=> 'REV_LEVEL, PRODUCTION_DATE, PRODUCTION_STATUS'); END;16.2.3 Modifying Existing Column GroupsOnce you have created a column group, you can add and remove member columns (with the ADD_GROUPED_COLUMN and DROP_GROUPED_COLUMN procedures), and you can add or change the comment associated with the group (with the COMMENT_ON_COLUMN_GROUP procedure).16.2.3.1 The DBMS_REPCAT.ADD_GROUPED_COLUMN procedureThe ADD_GROUPED_COLUMN procedure adds a member column to a column group. You can call this procedure after you have created a new, empty column group with DBMS_REPCAT.DEFINE_COLUMN_GROUP, or if your schema or conflict resolution requirements change. Here's the specification:PROCEDURE DBMS_REPCAT.ADD_GROUPED_COLUMN (sname IN VARCHAR2, oname IN VARCHAR2, column_group IN VARCHAR2, {list_of_column_names IN VARCHAR2 | list_of_column_names IN dbms_repcat.varchar2s});Note that you must specify only one of the list_of_column_names parameters.Parameters are summarized in the following table. NameDescriptionsnameName of the schema that owns the replicated table.onameName of the table with the column_group.column_groupName of the column_group to which column(s) will be added.list_of_column_namesA comma-delimited list of column names, or a PL/SQL table of column names. Use "*" to add all columns in the table to the column group.16.2.3.1.1 ExceptionsADD_GROUPED_COLUMN may raise the following exceptions:NameNumberDescriptionnonmasterdef-23312Invoking site is not master definition sitemissingobject-23308Table oname does not existmissinggroup-23331Column group column_group does not existmissingcolumn-23334Column(s) specified do not exist in table onameduplicatecolumn-23333Column(s) specified already exist in column_groupmissingschema-23306Schema sname does not exist16.2.3.1.2 RestrictionsNote the following restrictions on calling ADD_GROUPED_COLUMN:You must call this procedure from the quiesced master definition site.You must regenerate replication support for the table after defining the column group with the GENERATE_REPLICATION_SUPPORT procedure.16.2.3.1.3 ExampleIn this example, we add the columns CATALOG_ID and DESCRIPTION to the column group CG_PRODUCT_MFG_COLS that we created in the MAKE_COLUMN_GROUP example: DECLARE cg_list DBMS_REPCAT.VARCHAR2(s); BEGIN cg_list(1) := 'CATALOG_ID'; cg_list(1) := 'DESCRIPTION'; DBMS_REPCAT.ADD_GROUPED_COLUMN(sname=> 'SPROCKET', oname => 'PRODUCTS ', column_group => 'CG_PRODUCT_MFG_COLS', list_of_column_names => cg_list); END;16.2.3.2 The DBMS_REPCAT.DROP_GROUPED_COLUMN procedureThe DROP_GROUPED_COLUMN procedure allows you to drop a column from a column group. Dropping a column from a column group is quite similar to adding one. Make sure, however, that none of your conflict resolution methods reference the column(s) that you are dropping. And as with the other procedures with a "list_of_column_names" parameter, you can pass "*" to the parameter to indicate all fields in table oname. Here's the specification:PROCEDURE DBMS_REPCAT.DROP_GROUPED_COLUMN (sname IN VARCHAR2, oname IN VARCHAR2, column_group IN VARCHAR2, {list_of_column_names IN VARCHAR2 | list_of_column_names IN dbms_repcat.varchar2s});Note that you must specify only one of the list_of_column_names parameters.Parameters are summarized in the following table. NameDescriptionsnameName of the schema that owns the replicated tableonameName of the table with the column_groupcolumn_groupName of the column_group from which column(s) will be droppedlist_of_column_namesA comma-delimited list of column names, or a PL/SQL table of column names16.2.3.2.1 ExceptionsThe DROP_GROUPED_COLUMN procedure may raise the following exceptions:NameNumberDescriptionmissinggroup-23331Column group column_group does not existmissingobject-23308Table oname does not existmissingschema-23306Schema sname does not existnonmasterdef-23312Invoking site is not the master definition site16.2.3.2.2 RestrictionsNote the following restrictions on calling DROP_GROUPED_COLUMN:You must not call this procedure from the quiesced master definition site.You must regenerate replication support for the table after defining the column group with the GENERATE_REPLICATION_SUPPORT procedure.16.2.3.2.3 ExampleThe following example shows how to drop a column from an existing column group: BEGIN DBMS_REPCAT.DROP_GROUPED_COLUMN( sname => 'SPROCKET', oname => 'PRODUCTS', column_group => 'CG_PRODUCT_MFG_COLS', list_of_column_names => 'CATALOG_ID, DESCRIPTION'); END;16.2.3.3 The DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP procedureThe COMMENT_ON_COLUMN_GROUP procedure adds or changes the comment associated with a column group. Here's the specification:PROCEDURE DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP (sname IN VARCHAR2, oname IN VARCHAR2, column_group IN VARCHAR2, comment IN VARCHAR2);Parameters are summarized in the following table.NameDescriptionsnameName of the schema to which the replicated table belongsonameName of the replicated table containing the column groupcolumn_groupName of the column groupcommentComment16.2.3.3.1 ExceptionsThe COMMENT_ON_COLUMN_GROUP procedure may raise the following exceptions:NameNumberDescriptionmissinggroup-23331The column_group does not existnonmasterdef-23312Calling site is not the master definition site16.2.3.3.2 RestrictionsThe COMMENT_ON_COLUMN_GROUP procedure must be called from the master definition site.16.2.3.3.3 ExampleYou can create or change the comment field in DBA_REPCOLUMN_GROUP with the COMMENT_ON_COLUMN_GROUP procedure, as the following example illustrates: BEGIN DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP( sname => 'SPROCKET', oname => 'PRODUCTS', column_group => 'CG_PRODUCT_MFG_COLS', comment => 'Added catalog_id + desc on '||sysdate);16.1 Getting Started with DBMS_REPCAT16.3 Priority Groups with DBMS_REPCAT Copyright (c) 2000 O'Reilly & Associates. All rights reserved.