Build Deployment Template

This section contains a complete script example of how to construct a deployment template using the replication management API.

See Also: Chapter 4, "Deployment Templates Concepts & Architecture" in Oracle8i Replication for conceptual and architectural information about deployment templates.

Figure 4-1 Create Deployment Template

Text description of the illustration rardt2a.gif.

Be sure to read the comments contained within the scripts, as they contain important and useful information about building templates with the replication management API.

Note: Vertical partitioning is not supported using the replication management API. See "Vertical Partitioning" in Oracle8i Replication for more information.

--This script creates a private deployment template that contains
--four template objects, two template parameters, a set of user
--parameter values, and an authorized user.  A template is
--built in the following order:
--
--STEP 1: Define Refresh Group Template
--STEP 2: Add template objects to DT_PERSONNEL
--STEP 3: Define Parameter Defaults and Prompt Text
--STEP 4: Define User Parameter Values
--STEP 5: Authorize Users for Private Template


CONNECT repadmin/repadmin@orc3.world

/*************************************************************************
STEP 1:
CREATE DEPLOYMENT TEMPLATE
*************************************************************************/

--Before you begin assembling the components of your deployment
--template, use the CREATE_RERESH_TEMPLATE procedure to define the name of 
--your deployment template, along with several other template characteristics 
--(Public/Private status, target refresh group, and owner).

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE (
           owner => 'SCOTT',
           refresh_group_name => 'PERSONNEL',
           refresh_template_name => 'DT_PERSONNEL',
           template_comment => 'Personnel Deployment Template',
           public_template => 'N');
END;
/

/*************************************************************************
STEP 2:
ADD OBJECTS TO TEMPLATE
*************************************************************************/

--STEP 2a: Create EMP Snapshot

--The following procedure uses the DBMS_LOB package. This package is required 
--to insert values into the DDL_TEXT parameter of the CREATE_TEMPLATE_OBJECT 
--function, which has a CLOB datatype.  You will see the DBMS_LOB package 
--used whenever a value must be inserted into a CLOB parameter. For more 
--information about using the DBMS_LOB package and LOBs in general, see 
--Oracle8i Application Developer's Guide - Fundamentals. 

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_emp AS SELECT 
      empno, ename, job, mgr, hiredate, sal, comm, deptno 
      FROM scott.emp@:dblink WHERE deptno = :dept';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_emp',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--Whenever you create a snapshot, always specify the schema name of the table
--owner in the query for the snapshot. In the example above, SCOTT is specified
--as the owner of the EMP table.


--STEP 2b: Create DEPT Snapshot

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_dept AS SELECT 
      deptno, dname, loc 
      FROM scott.dept@:dblink';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_dept',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 2c: Create SALGRADE Snapshot

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_salgrade AS SELECT 
      grade, losal, hisal
      FROM scott.salgrade@:dblink';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_salgrade',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 2d: Create BONUS Snapshot

DECLARE
   tempstring VARCHAR2(300);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'CREATE SNAPSHOT scott.snap_bonus AS SELECT 
      ename, job, sal, comm
      FROM scott.bonus@:dblink';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'DT_PERSONNEL',
           object_name => 'snap_bonus',
           object_type => 'SNAPSHOT',
           ddl_text => templob,
           master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

/*************************************************************************
STEP 3:
DEFINE PARAMETER DEFAULTS
*************************************************************************/

--Rather than using the "CREATE" functions and procedures as in the 
--other steps, you use the ALTER_TEMPLATE_PARM procedure to define 
--a template parameter value and prompt string. You use the  
--"ALTER" procedure because the actual parameter was created in 
--step 2. Recall that you defined the :dblink and :dept parameters
--in the DDL_TEXT parameter. Oracle detects these parameters in
--the DDL and automatically creates the template parameter. Use
--the ALTER_TEMPLATE_PARM procedure to define the remainder of the
--template parameter information (that is, default parameter value 
--and prompt string).

--STEP 3a: DEPT Parameter

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := '20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM (
      refresh_template_name => 'DT_PERSONNEL',
      parameter_name => 'DEPT',
      new_default_parm_value => templob,
      new_prompt_string => 'Enter your department number:',
      new_user_override => 'Y');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

--STEP 3b: DBLINK Parameter

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'ORC2.WORLD';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM (
      refresh_template_name => 'DT_PERSONNEL',
      parameter_name => 'DBLINK',
      new_default_parm_value => templob,
      new_prompt_string => 'Enter target database link:',
      new_user_override => 'N');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

/*************************************************************************
STEP 4:
DEFINE USER PARAMETER VALUES
*************************************************************************/

--To automate the instantiation of custom data sets at
--individual remote snapshot sites, you can define USER
--PARAMETER values that will be used automatically when
--the specified user instantiates the target template.
--The CREATE_USER_PARM_VALUE procedure enables you to assign 
--a value to a parameter for a user.

--STEP 4a: Define User Parameter Value for user SCOTT

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := '30';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'DT_PERSONNEL',
           parameter_name => 'DEPT',
           user_name => 'SCOTT',
           parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/
--STEP 4b: Define User Parameter Value for user SCOTT

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, dbms_lob.session);
   tempstring := 'ORC2.WORLD';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'DT_PERSONNEL',
           parameter_name => 'DBLINK',
           user_name => 'SCOTT',
           parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

/*************************************************************************
STEP 5:
AUTHORIZE USERS FOR PRIVATE TEMPLATE
*************************************************************************/

--Because this is a private template (PUBLIC_TEMPLATE => 'N'
--in the DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE function
--defined in STEP 1), you need to authorize users to
--instantiate the DT_PERSONNEL deployment template. Use
--the DBMS_REPCAT_RGT.CREATE_USER_AUTHORIZATION function
--to create authorized users.

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_USER_AUTHORIZATION (
           user_name => 'SCOTT',
           refresh_template_name => 'DT_PERSONNEL');
END;
/

COMMIT;