15.3.2.1.1 Exceptions

The CREATE_MASTER_REPGROUP procedure may raise the following exceptions:

15.3.2.1.2 Restrictions

You must be connected to the replication administrator account (typically REPADMIN) to call CREATE_MASTER_REPGROUP.

15.3.2.1.3 Example

The following call creates a replication group named SPROCKET:

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPGROUP(	gname=> 'SPROCKET', -
	group_comment => 'Replication group SPROCKET created on
		'||sysdate|| ' by ' ||user, -
	master_comment => 'Master  Definition Site created on
	   '||sysdate|| ' by ' ||user);
END;

This call creates a replication group with no objects. The site from which you make the call is the master definition site for the group.

For an additional example, see the repgroup.sql file on the companion disk. That example queries the DBA_REPGROUP data dictionary view and lists all replication groups in the database.

15.3.2.2.1 Exceptions

The DROP_MASTER_REPGROUP procedure raises the following exceptions:

15.3.2.2.2 Restrictions

Note the following restrictions on calling DROP_MASTER_REPGROUP:

• You must be connected to the replication administrator account (typically REPADMIN) to call DROP_MASTER_REPGROUP.

• DROP_MASTER_REPGROUP does not drop all snapshots if the gname parameter is the master of any snapshot groups. Dropping a master site does not necessarily remove it from the DBA_REPSITES at other masters.

TIP: Before calling DROP_MASTER_REPGROUP, call DBMS_REPCAT.REMOVE_MASTER_DATABASES from the master definition site to remove all masters for which you plan to drop the group and that do not contain any other replication groups. In addition, you can avoid the full queue error by quiescing the replication group before attempting to drop the replication group.

15.3.2.2.3 Example

This call, from the master definition site, drops a replication group from all sites where it exists:

BEGIN
    DBMS_REPCAT.DROP_MASTER_REPGROUP(
        gname => 'SPROCKET',
        all_sites => TRUE );
END;

The next call drops a replication group and all of its objects from the calling site, assumed to be a master site (not a master definition site):

BEGIN
    DBMS_REPCAT.DROP_MASTER_REPGROUP(
        gname => 'SPROCKET',
        drop_contents => TRUE );
END;

If you want to drop a replication group from all master sites, along with the replicated objects, you can do the following:

BEGIN
    DBMS_REPCAT.DROP_MASTER_REPGROUP(
        gname => 'SPROCKET',
        all_sites => TRUE
        drop_contents => TRUE );
 END;

15.3.2.3.1 Exceptions

The COMMENT_ON_REPROUP procedure may raise the following exceptions:

15.3.2.3.2 Restrictions

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

15.3.2.3.3 Example

This call adds or replaces the comment in DBA_REPGROUP for the SPROCKET replication group:

BEGIN
    DBMS_REPCAT.COMMENT_ON_REPGROUP(
        gname 'SPROCKET',
        comment => 'Comment added on '||sysdate|| ' by '||user);
END;

COMMENT_ON_REPGROUP queues an RPC to update the field at all other master sites.

15.3.3.1.1 Exceptions

The CREATE_MASTER_REPOBJECT procedure may raise the following exceptions:

15.3.3.1.2 Restrictions

Note the following restrictions on calling CREATE_MASTER_REPOBJECT:

• This procedure must be called from the master definition site.

• The replication group must already exist and be quiesced.

15.3.3.1.3 Example

This section contains a series of examples showing how to create replication objects.

15.3.3.1.4 Adding an existing table to a replication group

This call adds table SPROCKET. PRODUCTS to the replication group SPROCKET:

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(sname   =>    'SPROCKET', 
                                        oname   =>    'PRODUCTS', 
                                        type    =>    'TABLE', 
                                        gname   =>    'SPROCKET');
END;

Since we have not specified ddl_text in this example, the table must already exist.

15.3.3.1.5 Creating an object at the master definition site

In this next example, we use CREATE_MASTER_REPOBJECT to create an object at the master definition site and add it to the replication group:

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
		sname 		=> 	'SPROCKET', 
		oname 		=>	'STATES', 
		type		=>	'TABLE'
		ddl_text	=>	'CREATE TABLE sprocket.states(state_id VARCHAR2(2),
   						state_name VARCHAR2(20))',
		gname 		=>	'SPROCKET');
END;

Notice that the CREATE TABLE statement in this example specifies the owner of the table. Typically, the replication administrator account uses DBMS_REPCAT, not the owner of the replicated schema. When this is the case, you must be sure to specify the schema in which to create objects. One of the privileges granted through DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP is CREATE ANY TABLE.

In all likelihood, you will not create objects with the CREATE_MASTER_REPOBJECT procedure very often, because doing so is rather clumsy for all but the most simple objects. But it's there if you want it.

Setting the retry and use_existing_object parameters to TRUE in this third example creates the table PRODUCTS at all master sites where it does not already exist; setting copy_rows to TRUE copies the data from the master definition site to the master sites.

BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
			sname 			=> 	'SPROCKET', 
			oname 			=>	'PRODUCTS', 
			type			=>	'TABLE', 
			use_existing_object 	=>	TRUE,
			retry			=>	TRUE,
			copy_rows		=>	TRUE,
			gname 			=>	'SPROCKET');
END;

If tables exist at master sites, but do not have the same definition as at the master definition site, Oracle returns an error.

NOTE: If you are incorporating an existing database into a replication group, you should consider precreating all of the objects at the new site manually, especially if the objects have interdependencies. At my sites, we always run a "catalog" script to create all schema objects, including triggers, primary and foreign key definitions, check constraints, etc. We then let Oracle generate the replication support objects. This methodology gives us complete control over how the schema is created, and we can easily reproduce the objects in other environments.

15.3.3.1.6 Replicating a package

In this final example, we replicate a package. To replicate a package, you must make two calls to CREATE_MASTER_REPOBJECT, one for the package, and one for the package body.

	BEGIN
		DBMS_REPCAT.CREATE_MASTER_REPOBJECT
			sname						=> 'SPROCKET',
			oname						=> 'PRODUCTMAINT',
			type						=> 'PACKAGE',
			use_existing_object				=> TRUE,
			comment						=> 'Added on '||sysdate,
			retry						=> FALSE,
			gname						=> 'SPROCKET');
 
		DBMS_REPCAT.CREATE_MASTER_REPOBJECT
			sname						=> 'SPROCKET',
			oname						=> 'PRODUCTMAINT',
			type						=> 'PACKAGE BODY', 
			use_existing_object				=> TRUE,
			comment						=> 'Added on '||sysdate,
			retry						=> FALSE,
			gname						=> 'SPROCKET');
	END;

For an additional example, see the repobjs.sql file on the companion disk. The example queries the DBA_REPOBJECT data dictionary view and lists all replicated objects in the database.

15.3.3.2.1 Exceptions

DBMS_REPCAT.SET_COLUMNS may raise the following exceptions:

15.3.3.2.2 Restrictions

Note the following restrictions on calling DBMS_REPCAT.SET_COLUMNS.

• DBMS_REPCAT.SET_COLUMNS must be run from the master definition site.

• The changes do not take effect until the next call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT.

15.3.3.2.3 Example

The following call designates columns COLOR, MODEL, and YEAR as the pseudo-primary key columns in table SPROCKET.PRODUCTS:

BEGIN
	DBMS_REPCAT.SET_COLUMNS(sname		=> 'SPROCKET',
				oname		=> 'PRODUCTS', 
				 column_list	=> 'COLOR,MODEL,YEAR');
END;

15.3.3.3.1 Exceptions

The DROP_MASTER_REPOBJECT procedure may raise the following exceptions:

15.3.3.3.2 Restrictions

Note the following restrictions on calling DROP_MASTER_REPOBJECT:

• This procedure must be called from the master definition site.

• The replication group must already exist and be quiesced.

15.3.3.3.3 Examples

The following call removes table SPROCKET.PRODUCTS from the SPROCKET replication group, but preserves the table:

	BEGIN
		DBMS_REPCAT.DROP_MASTER_REPOBJECT(
					sname 		=> 'SPROCKET',
					oname		=> 'PRODUCTS',
					type		=> 'TABLE');
	END;

Dropping a table from a replication group automatically drops all replication triggers associated with the table and removes it from the replication data dictionary views.

The DROP_MASTER_REPOBJECT procedure can remove the object from the replication group, and also drop the object from the schema by setting the drop_objects parameter to TRUE, as shown in this example:

BEGIN
	DBMS_REPCAT.DROP_MASTER_REPOBJECT(
				sname 			=> 'SPROCKET',
				oname			=> 'PRODUCTS',
				type			=> 'TABLE',
				drop_objects		=>  TRUE);
END;

15.3.3.4.1 Exceptions

The EXECUTE_DDL procedure may raise the following exceptions:

15.3.3.4.2 Restrictions

Note the following restrictions on calling EXECUTE_DDL:

• This procedure must be called from the master definition site.

• The replication group must already exist.

NOTE: The environment does not have to be quiesced.

15.3.3.4.3 Example

This example creates an index on the SPROCKET.STATES table at sites D7CA.BIGWHEEL.COM and D7NY.BIGWHEEL.COM. Note that as in the example of CREATE_MASTER_REPOBJECT, we must specify the schema in which to create the index.

DECLARE vMasters VARCHAR2(30);
BEGIN
	vMasters := 'D7CA.BIGWHEEL.COM,D7NY.BIGWHEEL.COM';
	DBMS_REPCAT.EXECUTE_DDL(
		gname			=> 'SPROCKET',
		master_list		=> vMasters,
		ddl_text		=>'CREATE INDEX sprocket.i_state_id ON
		   sprocket.tstates(state_id)',
		sname			=>'SPROCKET');
END;

15.3.3.5.1 Exceptions

The ALTER_MASTER_REPOBJECT procedure may raise the following exceptions:

15.3.3.5.2 Restrictions

Note the following restrictions on calling ALTER_MASTER_REPOBJECT:

• This procedure must be run from the master definition site.

• The replication group must be quiesced.

• You must call DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for the altered object before resuming replication.

15.3.3.5.3 Example

If you set the retry parameter to TRUE, ALTER_MASTER_REPOBJECT applies the DDL only at sites where the object has a status of INVALID in the DBA_OBJECTS data dictionary view.

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT(
		sname		=> 'SPROCKET',
		oname		=> 'PRODUCTMAINT',
		type		=> 'PACKAGE BODY'
		ddl_text	=> 'ALTER PACKAGE SPROCKET.PRODUCTMAINT COMPILE BODY',
		comment		=> 'Recompiled on '||sysdate|| ' by '||user,
		retry		=> TRUE );
END;

Notice that we specify the schema for the object that we are altering. As with DBMS_REPCAT.EXECUTE_DDL, the ALTER_MASTER_REPOBJECT procedure operates on objects in the caller's schema by default, and the caller is generally the replication administrator account, not the schema account.

This example alters the width of the state_id column in table SPROCKET.STATES at all sites:

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT(
		sname		=> 'SPROCKET',
		oname		=> 'PRODUCTMAINT',
		type		=> 'PACKAGE BODY'
		ddl_text	=> 'ALTER TABLE SPROCKET.STATES MODIFY
			 (STATE_ID NUMBER(10))' ,
		comment		=> 'state_id widened on '||sysdate|| ' by '||user);
END;

15.3.3.6.1 Exceptions

The COMMENT_ON_REPOBJECT procedure may raise the following exceptions:

15.3.3.6.2 Restrictions

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

15.3.3.6.3 Example

The following call updates the comment for replicated table SPROCKET.PRICES:

BEGIN
    DBMS_REPCAT.COMMENT_ON_REPOBJECT(
			sname		=>	'SPROCKET', -
			oname 		=>	'PRICES', -
			type		=>	'TABLE', -
			comment		=>	'Headquarters updates this table once a month.');
END;

15.3.4.1.1 Exceptions

The GENERATE_REPLICATION_SUPPORT procedure may raise the following exceptions:

15.3.4.1.2 Restrictions

Note the following restrictions on calling GENERATE_REPLICATION_SUPPORT:

• You must call this procedure from the master definition site for each object in the replication group.

• The replication group must be quiesced.

• If the object is not owned by the replication administrator account, the owner must have explicit EXECUTE privileges on the DBMS_DEFER package (described in Chapter 17).

• If the INIT.ORA parameter COMPATIBLE is 7.3 or higher, the distributed parameter must be set to TRUE.

• If the INIT.ORA parameter COMPATIBLE is less than 7.3 in any snapshot sites, the gen_rep2_trigger parameter must be set to TRUE, and the COMPATIBLE parameter at the master definition site must be set to 7.3.0.0 or greater.

15.3.4.1.3 Example

Suppose that we have a table SPROCKET.REON defined as follows:

Assuming that we have already added this table to the SPROCKET replication group, here is how we would generate replication support for it:

BEGIN
EXECUTE dbms_repcat.generate_replication_support( -
		sname => 'SPROCKET',	-
		oname => 'REGION',	-
		type => 'TABLE', 	-
		distributed => TRUE,	-
		gen_objs_owner=> 'SPROCKET',	-
		gen_rep2_trigger=> FALSE);
END;

This call creates a trigger and three packages, as described in the following table. Oracle immediately creates these objects at the master definition site, as well as the participating master sites.

In the next sections, we examine what Oracle creates for us when we generate replication support for this table.

15.3.4.1.4 The replication support trigger

The following example shows the text of the REGION$RT trigger that the GENERATE_REPLICATION_SUPPORT call generates:

declare
  flag char;
begin
  if "REGION$TP".active then
    if inserting then
      flag := 'I';
    elsif updating then
      flag := 'U';
    elsif deleting then
      flag := 'D';
    end if;
    "REGION$TP".replicate(
      :old."REGION_ID",:new."REGION_ID",
      :old."REGION_NAME",:new."REGION_NAME",
      flag);
  end if;
end;

As you can see, this BEFORE ROW trigger simply sets a flag to indicate the type of DML being performed: "I" for inserts, "U" for updates, and "D" for deletes. It then passes this flag, along with the new and old values of each field, to the REGIONS$TP.REPLICATE procedure.

15.3.4.1.5 The replication support packages

The replication package REGION$TP invokes DBMS_SNAPSHOT.I_AM_A_REFRESH (see the description of this procedure in Chapter 14) to determine if the DML that fired the REGION$RT trigger originated locally, or if it is DML that another site has propagated. If the DML originated locally, then the REGION$TP builds a deferred call -- one of the REGION$RP procedures (REP_UPDATE, REP_INSERT, or REP_DELETE), as appropriate. Oracle queues this deferred call to all master sites. Here is the Oracle-generated code:

package body		"REGION$TP" as
  I_am_a_snapshot CHAR;
  is_snapshot BOOLEAN;
  function active return boolean
  is
  begin
    return (not((is_snapshot and dbms_snapshot.I_am_a_refresh) or
		not dbms_reputil.replication_is_on));
  end active;
  procedure replicate(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    flag IN CHAR)
  is
  begin
    if flag = 'U' then
      dbms_defer.call('SPROCKET','REGION$RP','REP_UPDATE',6,'SPROCKET');
      dbms_defer.number_arg("REGION_ID1_o");
      dbms_defer.number_arg("REGION_ID1_n");
      dbms_defer.varchar2_arg("REGION_NAME2_o");
      dbms_defer.varchar2_arg("REGION_NAME2_n");
    elsif flag = 'I' then
      dbms_defer.call('SPROCKET','REGION$RP','REP_INSERT',4,'SPROCKET');
      dbms_defer.number_arg("REGION_ID1_n");
      dbms_defer.varchar2_arg("REGION_NAME2_n");
    elsif flag = 'D' then
      dbms_defer.call('SPROCKET','REGION$RP','REP_DELETE',4,'SPROCKET');
      dbms_defer.number_arg("REGION_ID1_o");
      dbms_defer.varchar2_arg("REGION_NAME2_o");
    end if;
    dbms_defer.varchar2_arg(dbms_reputil.global_name);
    dbms_defer.char_arg(I_am_a_snapshot);
  end replicate;
begin
  select decode(master, 'N', 'Y', 'N')
    into I_am_a_snapshot
    from all_repcat where gname = 'SPROCKET';
  is_snapshot := (I_am_a_snapshot = 'Y');
end "REGION$TP";

Notice that Oracle passes the old and new values of each column in the table to the REGION$RP procedure. Oracle uses these values to confirm that the version of the row at the originating site is the same as the version of the row at the destination sites. If the old column values at the originating site do not match the current column values at the destination site, then Oracle detects a conflict and invokes the appropriate conflict resolution method.

You can see this logic in the package body of REGION$RP:

package body		"REGION$RP" as
  procedure rep_delete(
    "REGION_ID1_o" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) is
  begin
    if propagation_flag = 'N' then
      dbms_reputil.replication_off;
    end if;
    dbms_reputil.rep_begin;
    dbms_reputil.global_name := site_name;
    delete from "REGION"
    where ("REGION_ID1_o" = "REGION_ID"
    and   "REGION_NAME2_o" = "REGION_NAME");
    if sql%rowcount = 0 then
      raise no_data_found;
    elsif sql%rowcount > 1 then
      raise too_many_rows;
    end if;
    dbms_reputil.rep_end;
  exception
    when no_data_found then
      begin
	if not "REGION$RR".delete_conflict_handler(
	  "REGION_ID1_o",
	  "REGION_NAME2_o",
	  site_name,
	  propagation_flag) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when others then
      dbms_reputil.rep_end;
      raise;
  end rep_delete;
  procedure rep_insert(
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) is
  begin
    if propagation_flag = 'N' then
      dbms_reputil.replication_off;
    end if;
    dbms_reputil.rep_begin;
    dbms_reputil.global_name := site_name;
    insert into "REGION" (
      "REGION_ID",
      "REGION_NAME")
    values (
      "REGION_ID1_n",
      "REGION_NAME2_n");
    dbms_reputil.rep_end;
  exception
    when dup_val_on_index then
      begin
	if not "REGION$RR".unique_conflict_insert_handler(
	  "REGION_ID1_n",
	  "REGION_NAME2_n",
	  site_name,
	  propagation_flag,
	  SQLERRM) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when others then
      dbms_reputil.rep_end;
      raise;
  end rep_insert;
  procedure rep_update(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) is
  begin
    if propagation_flag = 'N' then
      dbms_reputil.replication_off;
    end if;
    dbms_reputil.rep_begin;
    dbms_reputil.global_name := site_name;
    update "REGION" set
      "REGION_ID" = "REGION_ID1_n",
      "REGION_NAME" =
	decode("REGION_NAME2_o",
	       "REGION_NAME2_n", "REGION_NAME",
	       "REGION_NAME2_n")
    where (((1 = 1 and
	    "REGION_NAME2_o" = "REGION_NAME2_n")) or
	   (1 = 1 and
	    "REGION_NAME2_o" = "REGION_NAME"))
      and "REGION_ID1_o" = "REGION_ID";
    if sql%rowcount = 0 then
      raise no_data_found;
    elsif sql%rowcount > 1 then
      raise too_many_rows;
    end if;
    dbms_reputil.rep_end;
  exception
    when no_data_found then
      begin
	if not "REGION$RR".update_conflict_handler(
	  "REGION_ID1_o",
	  "REGION_ID1_n",
	  "REGION_NAME2_o",
	  "REGION_NAME2_n",
	  site_name,
	  propagation_flag) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when dup_val_on_index then
      begin
	if not "REGION$RR".unique_conflict_update_handler(
	  "REGION_ID1_o",
	  "REGION_ID1_n",
	  "REGION_NAME2_o",
	  "REGION_NAME2_n",
	  site_name,
	  propagation_flag,
	  SQLERRM) then
	  dbms_reputil.rep_end;
	  raise;
	end if;
	dbms_reputil.rep_end;
      exception
	when others then
	  dbms_reputil.rep_end;
	  raise;
      end;
    when others then
      dbms_reputil.rep_end;
      raise;
  end rep_update;
end "REGION$RP";

As you can see, Oracle invokes REGION$RR, the conflict resolution package:

package body		"REGION$RR" as
  function unique_conflict_insert_handler(
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR,
    errmsg IN VARCHAR2) return boolean is
  begin
    return FALSE;
  end unique_conflict_insert_handler;

  function delete_conflict_handler(
    "REGION_ID1_o" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) return boolean is
  begin
    return FALSE;
  end delete_conflict_handler;

  function update_conflict_handler(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR) return boolean is
  begin
    return FALSE;
  end update_conflict_handler;

  function unique_conflict_update_handler(
    "REGION_ID1_o" IN NUMBER,
    "REGION_ID1_n" IN NUMBER,
    "REGION_NAME2_o" IN VARCHAR2,
    "REGION_NAME2_n" IN VARCHAR2,
    site_name IN VARCHAR2,
    propagation_flag IN CHAR,
    errmsg IN VARCHAR2) return boolean is
  begin
    return FALSE;
  end unique_conflict_update_handler;
end "REGION$RR";

This is the default conflict handling package that GENERATE_REPLICATION_SUPPORT creates. Since no conflict resolution methods are defined for REGION, the unique_conflict_insert_handler, delete_conflict_handler, update_conflict_handler, and unique_conflict_update_handler programs all return FALSE, indicating that they cannot resolve the conflict. Chapter 17 contains details about how to define conflict resolution handlers.

15.3.4.1.6 Generating replication support for packages and procedures

As well as tables, you can also replicate procedures and packages. When you call a replicated procedure, Oracle builds a deferred RPC that it propagates to all master sites. This deferred RPC invokes the same procedure with the same arguments as the originating call. Oracle recommends procedural replication for situations that call for massive updates to tables (i.e., updates affecting tens of thousands of rows). Procedural replication duplicates the procedure call only, which is more efficient and network-friendly than row-level replication. (Row-level replication sends the old and new column values for every field of every row.)

Just as we made two calls to CREATE_MASTER_REPOBJECT to create a replicated package, we must also make two calls to GENERATE_REPLICATION_SUPPORT:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT( 
			sname		=> 'SPROCKET',
			oname		=> 'PRODUCTMAINT',
			type		=> 'PACKAGE',
			distributed	=> TRUE,
			gen_objs_owner	=> 'SPROCKET',
			gen_rep2_trigger=> FALSE);   
 
		DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
			sname		=> 'SPROCKET',
			oname		=> 'PRODUCTMAINT',
			type		=> 'PACKAGE BODY',
			distributed	=> TRUE,
			gen_objs_owner	=> 'SPROCKET',
			gen_rep2_trigger=> FALSE);
	END;

These two calls create a "wrapper" package and package body, named DEFER_PRODUCTMAINT. This package uses DBMS_DEFER.CALL (described in Chapter 17) to build RPCs to PRODUCTMAINT. To replicate a call to procedure ADDPRODUCT, we would call DEFER_PRODUCTMAINT.ADDPRODUCT.

package "DEFER_PRODUCTMAINT" as
  I_am_a_snapshot CHAR;
  procedure "ADDPRODUCT"(
    "PRODUCT_TYPE_IN" IN number,
    "CATALOG_ID_IN" IN varchar2,
    "DESCRIPTION_IN" IN varchar2,
    "REV_LEVEL_IN" IN varchar2,
    "PRODUCTION_DATE_IN" IN date,
    "PRODUCT_STATUS_IN" IN varchar2,
    call_local IN char := 'N',
    call_remote IN char := 'Y');
end "DEFER_PRODUCTMAINT";

package body "DEFER_PRODUCTMAINT" as
  procedure "ADDPRODUCT"(
    "PRODUCT_TYPE_IN" IN NUMBER,
    "CATALOG_ID_IN" IN VARCHAR2,
    "DESCRIPTION_IN" IN VARCHAR2,
    "REV_LEVEL_IN" IN VARCHAR2,
    "PRODUCTION_DATE_IN" IN DATE,
    "PRODUCT_STATUS_IN" IN VARCHAR2,
    call_local IN char := 'N',
    call_remote IN char := 'Y') is
  begin
    select decode(master, 'N', 'Y', 'N')
      into I_am_a_snapshot
      from all_repcat where gname = 'SPROCKET';
    if call_local = 'Y' then
      "SPROCKET"."PRODUCTMAINT"."ADDPRODUCT"(
	"PRODUCT_TYPE_IN",
	"CATALOG_ID_IN",
	"DESCRIPTION_IN",
	"REV_LEVEL_IN",
	"PRODUCTION_DATE_IN",
	"PRODUCT_STATUS_IN");
    end if;
    if call_remote = 'Y' then
	8, 'SPROCKET');
      dbms_defer.number_arg("PRODUCT_TYPE_IN");
      dbms_defer.varchar2_arg("CATALOG_ID_IN");
      dbms_defer.varchar2_arg("DESCRIPTION_IN");
      dbms_defer.varchar2_arg("REV_LEVEL_IN");
      dbms_defer.date_arg("PRODUCTION_DATE_IN");
      dbms_defer.varchar2_arg("PRODUCT_STATUS_IN");
      dbms_defer.char_arg('Y');
      dbms_defer.char_arg(I_am_a_snapshot);
    end if;
  end "ADDPRODUCT";
begin
  select decode(master, 'N', 'Y', 'N')
    into I_am_a_snapshot
    from all_repcat where gname = 'SPROCKET';
end "DEFER_PRODUCTMAINT";

15.3.4.2.1 Exceptions

The GENERATE_REPLICATON_PACKAGE procedure may raise the following exceptions:

15.3.4.2.2 Restrictions

Note the following restrictions on calling GENERATE_REPLICATION_PACKAGE:

• You must call this procedure from the master definition site.

• The replication group must be quiesced.

• The Oracle version must be 7.3 or later.

15.3.4.2.3 Example

The following call generates the replication support packages for table SPROCKET.PRODUCTS in all master sites:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_PACKAGE(
			sname	=> 'SPROCKET', 
			oname 	=> 'PRODUCTS');
	END;

15.3.4.3.1 Exceptions

The GENERATE_REPLICATION_TRIGGER procedure may raise the following exceptions:

15.3.4.3.2 Restrictions

Note the following restrictions on calling GENERATE_REPLICATION_TRIGGER:

• You must call this procedure from the master definition site.

• The replication group must be quiesced.

• The GENERATE_REPLICATION_SUPPORT or GENERATE_PACKAGE_SUPPORT must previously have been called for the object specified in the oname parameter.

15.3.4.3.3 Examples

The simplest invocation of the GENERATE_REPLICATION_TRIGGER procedure does the most work; this call generates replication triggers for all replicated tables at all master sites:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(gname=> 'SPROCKET' );
	END;

This next example generates replication triggers for the replicated table SPROCKET.PRODUCTS at all master sites:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(
		    gname    => 'SPROCKET',
		    oname    => 'PRODUCTS' );
	END;

The following call generates replication triggers for all replicated tables in the SPROCKET replication group at the master sites D7HI.BIGWHEEL.COM and D7WA.BIGWHEEL.COM:

	BEGIN
		DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(
		    gname    => 'SPROCKET',
		    master_list=> 'D7HI.BIGWHEEL.COM, D7WA.BIGWHEEL.COM' );
	END;

The following call regenerates the replication support for all objects in replication group SPROCKET at all master sites:

EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_TRIGGER(gname=> 'SPROCKET' )

For an additional example, see the invalids.sql file on the companion disk. The example lists all objects in the database with a status of INVALID and generates the appropriate SQL statements to attempt to validate them.

15.3.5.1.1 Exceptions

The ADD_MASTER_DATABASE procedure may raise the following exceptions:

15.3.5.1.2 Restrictions

Note the following restrictions on calling ADD_MASTER_DATABASE:

• This procedure must be run from the master definition site.

• The replication group must be quiesced.

15.3.5.1.3 Example

The ADD_MASTER_DATABASE procedure is relatively simple to use. For example, this call adds site D7NY.BIGWHEEL.COM to the SPROCKET replication group and instantiates all objects there:

	BEGIN
		DBMS_REPCAT.ADD_MASTER_DATABASE(
			gname		=> 'SPROCKET', 
			master		=> 'D7NY.BIGWHEEL.COM', 
			use_existing_objects=> 'FALSE', 
			copy_rows	=> 'TRUE', 
			propagation_mode=> 'ASYNCHRONOUS');
	END;

For additional examples, see the repsites.sql and links.sql files on the companion disk. The repsites.sql example queries all replication sites, sorted by replication group, and queries the DBA_REPSITES data dictionary view. The links.sql example lists all database links in the database (but not the passwords for those that were created using a CONNECT clause).

NOTE: It is generally easier to instantiate all objects at the new master site first. That way, the call to ADD_MASTER_DATABASE does not have to perform DDL to create the schema or send all of the data across a network link. If you instantiate the objects first, the call to ADD_MASTER_DATABASE has to generate replication support only for the objects at the new site and update other master sites with the new master's existence.

15.3.5.2.1 Exceptions

The REMOVE_MASTER_DATABASES procedure may raise the following exceptions:

15.3.5.2.2 Restrictions

The REMOVE_MASTER_DATABASES procedure must be run from the master definition site.

15.3.5.2.3 Example

To remove site D7NY.BIGWHEEL.COM from the SPROCKET replication group and inform all other master sites, specify the following:

	BEGIN
		DBMS_REPCAT.REMOVE_MASTER_DATABASES(
			name		=> 'SPROCKET', 
			master_list 		=> 'D7NY.BIGWHEEL.COM');
	END;

NOTE: After removing master sites with REMOVE_MASTER_DATABASES, you should call DBMS_REPCAT.DROP_MASTER_REPGROUP at each of the master sites you removed. Although you do not need to quiesce the replication group to remove one or more master database(s), you are strongly encouraged to do so. Otherwise, you will have to manually clear the RPC queue and resolve any inconsistencies.

15.3.5.3.1 Exceptions

The COMMENT_ON_REPSITES procedure may raise the following exceptions:

15.3.5.3.2 Restrictions

You must call the COMMENT_ON_REPSITES procedure from the master definition site.

15.3.5.3.3 Example

The following call updates the comment for master site D7NY. BIGWHEEL.COM:

	BEGIN
		DBMS_REPCAT.COMMENT_ON_REPSITES(
			gname		=> 'SPROCKET', 
			master		=> 'D7NY.BIGWHEEL.COM', 
			comment		=> 'Comment added on '||sysdate|| ' by ' ||user)
	END;

15.3.5.4.1 Exceptions

The RELOCATE_MASTERDEF procedure may raise the following exceptions:

15.3.5.4.2 Restrictions

You must call RELOCATE_MASTERDEF from a master or master definition site.

15.3.5.4.3 Example

The following call relocates the master definition site for replication group SPROCKET from D7CA.BIGWHEEL.COM to D7NY.BIGWHEEL.COM, and informs all masters, as well as the master definition site, of the change:

BEGIN
    DBMS_REPCAT.RELOCATE_MASTERDEF(
		gname		=> 'SPROCKET', 
		old_master_def	=> 'D7CA.BIGWHEEL.COM', 
		new_master_def	=> 'D7NY.BIGWHEEL.COM', 
		notify_masters	=> TRUE, 
		include_old_masterdef=> TRUE);
END;

Suppose that the master definition site D7CA.BIGWHEEL.COM becomes permanently unavailable. We can convert another site, such as D7NY.BIGWHEEL.COM, into the master definition site without having to communicate with D7CA.BIGWHEEL.COM. We set the include_old_masterdef parameter to FALSE.

BEGIN
    DBMS_REPCAT.RELOCATE_MASTERDEF(
		gname		=> 'SPROCKET', 
		old_master_def	=> 'D7CA.BIGWHEEL.COM', 
		new_master_def	=> 'D7NY.BIGWHEEL.COM', 
		notify_masters	=> TRUE, 
		include_old_masterdef=> FALSE);
END;

15.3.6.1.1 Exceptions

The DO_DEFERRED_REPCAT_ADMIN procedure may raise the following exceptions:

15.3.6.1.2 Restrictions

The DO_DEFERRED_REPCAT_ADMIN procedure performs only the procedures that have been queued by the invoking user. Note that the job queue is used to perform the queued procedures automatically.

15.3.6.1.3 Example.

If you want to run DO_DEFERRED_REPCAT_ADMIN manually, either because you do not have DBMS_JOB background processes running, or because you want to push the repcatlog queue immediately, you can do so. Here is an example:

BEGIN
    DBMS_REPCAT.DO_DEFFERED_REPCAT_ADMIN
	( gname => 'SPROCKET',  all_sites => TRUE);
END;

For an additional example, see the catlog.sql file on the companion disk. The example lists entries in the repcatlog (DBA_REPCATLOG) with the time of submission in hours, minutes, and seconds.

15.3.6.2.1 Exceptions

The WAIT_MASTER_LOG procedure may raise the following exception:

15.3.6.2.2 Example

The following call returns after 60 seconds, or after five entries have been entered into the DBA_REPCATLOG data dictionary view at the current master for replication group SPROCKET. The number of records (corresponding to incomplete tasks) is stored in the variable vRecCount.

	VARIABLE vRecCount NATURAL
	BEGIN
		DBMS_REPCAT.WAIT_MASTER_LOG(gname=> 'SPROCKET', 
						record_count => 5, 
						timeout => 60, 
						true_count => vRecCount);
	END;

NOTE: You might find it more convenient to query the DBA_REPCATLOG data dictionary view directly.

15.3.6.3.1 Exceptions

The PURGE_MASTER_LOG procedure may raise the following exception:

15.3.6.3.2 Restrictions

The calling site must be a master site.

15.3.6.3.3 Example

The following call removes all entries associated with replication group SPROCKET from the DBA_REPCATLOG data dictionary view:

	BEGIN
		DBMS_REPCAT.PURGE_MASTER_LOG(gname=> 'SPROCKET' );
	END;

For an additional example, see the caterr.sql file on the companion disk. The example lists entries in the repcatlog (DBA_REPCATLOG) containing errors, and displays the error message associated with each error.

NOTE: To clear all entries from the DBA_REPCATLOG data dictionary view, set all parameters to NULL.

15.3.7.1.1 Exceptions

The SUSPEND_MASTER_ACTIVITY procedure may raise the following exceptions:

15.3.7.1.2 Restrictions

Note the following restrictions on calling SUSPEND_MASTER_ACTIVITY:

• You must run this procedure from the master definition site.

• Prior to Oracle8, this procedure quiesces all replication groups at the master definition site, not just the group specified by the gname parameter.

15.3.7.1.3 Example

The following call suspends replication activity for the SPROCKET replication group:

BEGIN
    DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(  gname => 'SPROCKET' );
END;

NOTE: This call can take some time to complete if you have many master sites and/or many outstanding transactions. You can monitor the progress by querying the status field in the DBA_REPCATLOG data dictionary view.

15.3.7.2.1 Exceptions

The RESUME_MASTER_ACTIVITY procedure may raise the following exceptions:

15.3.7.2.2 Restrictions

Note the following restrictions on calling RESUME_MASTER_ACTIVITY:

• You must run this procedure from the master definition site.

• The replication group must be quiesced or quiescing.

15.3.7.2.3 Example

The following example resumes replication activity for a replication group:

BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY(  gname => 'SPROCKET' );
END;

15.3.8.1.1 Exceptions

The REPCAT_IMPORT_CHECK procedure may raise the following exceptions:

15.3.8.1.2 Example

The following call revalidates all replicated objects and supporting objects for the SPROCKET replication group. Issue this call after an import:

BEGIN
    DBMS_REPCAT.REPCAT_IMPORT_CHECJ(	sname						=> 'SPROCKET',  master	 => TRUE );
END;

NOTE: Call REPCAT_IMPORT_CHECK with sname and master set to NULL (or with no parameters) to validate all replication groups at the site.

15.3.8.2.1 Exceptions

The ALTER_MASTER_PROPAGATION procedure may raise the following exceptions:

15.3.8.2.2 Restrictions

Note the following restrictions on calling ALTER_MASTER_PROPAGATION:

• You must run this procedure from the master definition site.

• The replication group must be quiesced.

15.3.8.2.3 Example

The following call changes the propagation mode between D7CA.BIGWHEEL.COM and D7NY.BIGWHEEL.COM to SYNCHRONOUS:

	BEGIN
		DBMS_REPCAT.ALTER_MASTER_PROPAGATION(
			gname		=> 'SPROCKET', 
			master		=> 'D7CA.BIGWHEEL.COM', 
			dblink_list	=> 'D7NY.BIGWHEEL.COM', 
			use_existing_objects=> 'FALSE', 
			propagation_mode=> 'SYNCHRONOUS');
	END;

NOTE: If you change the propagation mode, you must also regenerate the replication support triggers for all replicated tables; ALTER_MASTER_PROPAGATION does not do this automatically. After altering the propagation method, you must call DBMS_REPCAT.GENERATE_REPLICATION_TRIGGERS for all replicated tables in the replication group.

15.3.8.3.1 Exceptions

The SEND_AND_COMPARE_OLD_VALUES procedure may raise the following exceptions:

15.3.8.3.2 Restrictions

Note the following restrictions on calling SEND_AND_COMPARE_OLD_VALUES:

• You must call this procedure from the master definition site.

• The replication group sname must be quiesced.