#

| Chapter 16 Conflict Resolution |

## 16.2 Column Groups with DBMS_REPCAT

Column 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 Groups

Suppose 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_COLUMN
DBMS_REPCAT.COMMENT_ON_COLUMN_GROUP
DBMS_REPCAT.DEFINE_COLUMN_GROUP
DBMS_REPCAT.DROP_COLUMN_GROUP
DBMS_REPCAT.DROP_GROUPED_COLUMN
DBMS_REPCAT.MAKE_COLUMN_GROUP

### 16.2.2 Creating and Dropping Column Groups

The 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 procedure

The 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.

Name | Description
--- | ---
sname | Name of the schema to which the replicated table belongs
oname | Name of the replicated table containing the column group
column_group | Name of the column group
comment | Comment

#### 16.2.2.1.1 Exceptions

The DEFINE_COLUMN_GROUP procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicategroup | -23330 | Column_group already exists
missingobject | -23308 | Object oname does not exist
nonmasterdef | -23312 | Calling site is not master definition site

#### 16.2.2.1.2 Restrictions

Note 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 Example

The 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 procedure

The 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.

Name | Description
--- | ---
sname | Name of the schema to which the replicated table belongs
oname | Name of the replicated table containing the column group
column_group | Name of the column group

#### 16.2.2.2.1 Exceptions

The DROP_COLUMN_GROUP procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missinggroup | -23331 | The column_group does not exist
missingobject | -23308 | The object oname does not exist
missingschema | -23306 | The schema sname does not exist
nonmasterdef | -23312 | Calling site is not master definition site
referenced | -23332 | The column_group is used by existing conflict resolution methods

#### 16.2.2.2.2 Restrictions

Note 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 Example

This 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 procedure

The 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.

Name | Description
--- | ---
sname | Name of the schema to which the replicated table belongs.
oname | Name of the replicated table containing the column group.
column_group | Name of the column group.
list_of_column_names | A 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 Exceptions

The MAKE_COLUMN_GROUP procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
duplicatecolumn | -23333 | Column(s) already a member of a different column group
duplicategroup | -23330 | column_group already exists
missingcolumn | -23334 | Column(s) specified do not exist in table oname
missingobject | -23308 | Object oname does not exist
nonmasterdef | -23312 | Calling site is not master definition site

#### 16.2.2.3.2 Restrictions

Note 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 Example

By 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 Groups

Once 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 procedure

The 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.

Name | Description
--- | ---
sname | Name of the schema that owns the replicated table.
oname | Name of the table with the column_group.
column_group | Name of the column_group to which column(s) will be added.
list_of_column_names | A 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 Exceptions

ADD_GROUPED_COLUMN may raise the following exceptions:

Name | Number | Description
--- | --- | ---
nonmasterdef | -23312 | Invoking site is not master definition site
missingobject | -23308 | Table oname does not exist
missinggroup | -23331 | Column group column_group does not exist
missingcolumn | -23334 | Column(s) specified do not exist in table oname
duplicatecolumn | -23333 | Column(s) specified already exist in column_group
missingschema | -23306 | Schema sname does not exist

#### 16.2.3.1.2 Restrictions

Note 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 Example

In 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 procedure

The 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.

Name | Description
--- | ---
sname | Name of the schema that owns the replicated table
oname | Name of the table with the column_group
column_group | Name of the column_group from which column(s) will be dropped
list_of_column_names | A comma-delimited list of column names, or a PL/SQL table of column names

#### 16.2.3.2.1 Exceptions

The DROP_GROUPED_COLUMN procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missinggroup | -23331 | Column group column_group does not exist
missingobject | -23308 | Table oname does not exist
missingschema | -23306 | Schema sname does not exist
nonmasterdef | -23312 | Invoking site is not the master definition site

#### 16.2.3.2.2 Restrictions

Note 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 Example

The 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 procedure

The 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.

Name | Description
--- | ---
sname | Name of the schema to which the replicated table belongs
oname | Name of the replicated table containing the column group
column_group | Name of the column group
comment | Comment

#### 16.2.3.3.1 Exceptions

The COMMENT_ON_COLUMN_GROUP procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
missinggroup | -23331 | The column_group does not exist
nonmasterdef | -23312 | Calling site is not the master definition site

#### 16.2.3.3.2 Restrictions

The COMMENT_ON_COLUMN_GROUP procedure must be called from the master definition site.

#### 16.2.3.3.3 Example

You 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_REPCAT |  | 16.3 Priority Groups with DBMS_REPCAT

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.