Oracle8i Replication Management API Reference Release 2 (8.1.6) A76958-01 |
|
Create Deployment Template, 3 of 5
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. |
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;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|