Create Deployment Template, 3 of 5

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

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;