Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 16.1 Getting Started with DBMS_REPCATChapter 16
Conflict Resolution
Next: 16.3 Priority Groups with DBMS_REPCAT
 

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);


Previous: 16.1 Getting Started with DBMS_REPCATOracle Built-in PackagesNext: 16.3 Priority Groups with DBMS_REPCAT
16.1 Getting Started with DBMS_REPCATBook Index16.3 Priority Groups with DBMS_REPCAT

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference