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.