4
Create a Deployment Templa te

*/

SET ECHO ON

SPOOL create_dt.out

CONNECT repadmin/repadmin@orc3.wo
rld

DECLARE
   a NUMBER;
BEGIN
   a
 := DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE (
           owner => 'hr',
           refresh_group_name => 'hr_refg',
           refresh_template_name => 'hr_refg_dt'
,
           template_comment => 'Human Resources Deployment Template',
           public_te
mplate => 'N');
END;
/

/*

Create countries_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.countries_mv 
      REFRESH FAST WITH PRIMARY KEY FO
R UPDATE AS SELECT 
      country_id, country_name, region_id 
      FROM hr.countries@:dblin
k';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (

    refresh_template_name => 'hr_refg_dt',
           object_name => 'countries_mv',

         object_type => 'MATERIALIZED VIEW',
           ddl_text => tempstring,

    master_rollback_seg => 'rbs');
END;
/

/*

Whenever you create a materialized view, always specify the schema name o f the table owner in the query for the materialized view. In the example previously, hr is specified as the owner of the countries table.

Create departments_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);

   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.departm
ents_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      department_id, depar
tment_name, manager_id, location_id
      FROM hr.departments@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',

           object_name => 'departments_mv',
           object_type => 'MATERIALIZED VI
EW',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Create employees_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
<
a name="7606">   tempstring := 'CREATE MATERIALIZED VIEW hr.employees_mv 
      REFRESH FAST WITH PRIMARY KEY
 FOR UPDATE AS SELECT 
      employee_id, first_name, last_name, email, phone_number, 
      h
ire_date, job_id, salary, commission_pct, manager_id, 
      department_id
      FROM hr.emplo
yees@:dblink WHERE department_id = :dept';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OB
JECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'e
mployees_mv',
           object_type => 'MATERIALIZED VIEW',
           ddl_text => temps
tring,
           master_rollback_seg => 'rbs');
END;
/
<
/a>
/*

Create jobs_mv materialized vi ew.

*/

DECLARE
   tempstring VARCH
AR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.
jobs_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      job_id, job_title, mi
n_salary, max_salary 
      FROM hr.jobs@:dblink';
   a := DBMS_R
EPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',

        object_name => 'jobs_mv',
           object_type => 'MATERIALIZED VIEW',

   ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;

/

/*

Create job_his tory_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstri
ng := 'CREATE MATERIALIZED VIEW hr.job_history_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      employee_id, start_date, end_date, job_id, department_id 
      FROM hr.job_history@:dblink
';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (

   refresh_template_name => 'hr_refg_dt',
           object_name => 'job_history_mv',

          object_type => 'MATERIALIZED VIEW',
           ddl_text => tempstring,

     master_rollback_seg => 'rbs');
 END;
/

/*

Create locations_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.locations_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      location_id, street_address, post
al_code, city, 
      state_province, country_id 
      FROM hr.locations@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_t
emplate_name => 'hr_refg_dt',
           object_name => 'locations_mv',
           obje
ct_type => 'MATERIALIZED VIEW',
           ddl_text => tempstring,
           master_ro
llback_seg => 'rbs');
END;
/

/*
<
/a>

Create regions_mv materialized view.

*/

DECLARE
   tempstring VARCHAR2(3000);
   a NU
MBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.regions_mv 

   REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      region_id, region_name 
      FR
OM hr.regions@:dblink';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'regions_mv',
           object_type => 'MATERIALIZED VIEW',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

/*

Step 3 Define parameter defaults.

Rather than using the CREATE_* functions and procedures as in the other steps, use the ALTER_TEMPLATE_PARM procedure to define a template parameter value and prompt string. You use the A LTER_* procedure because the actual parameter was created in Step 1 and 1. Recall that you defined the :dblink and :dept template 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 informa tion (that is, default parameter value and prompt string).

*/

BEGIN 
DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM ( 
      r
efresh_template_name => 'hr_refg_dt', 
      parameter_name => 'dblink', 
      new_def
ault_parm_value => 'orc3.world', 
      new_prompt_string => 'Enter your master site:', 
      new_user_override => 'Y'); 
END; 
/  

/*

To automate the instantiation of custom data sets at individual remote materialized view 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.

Complete the following tasks to define user parameter values.

Define dept user parameter value for user hr.

*/

DE
CLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT
_RGT.CREATE_USER_PARM_VALUE (
           refresh_template_name => 'hr_refg_dt',

     parameter_name => 'dept',
           user_name => 'hr',
           parm_value =>
; '20');
END;
/

/*

Define dblink user parameter value for user hr.

*/

DECLARE
   a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
           refresh_temp
late_name => 'hr_refg_dt',
           parameter_name => 'dblink',
           user_name
=> 'hr',
           parm_value => 'orc3.world');
END;
/

/*

Step 5 Authori ze users for private template.

Because this is a private template (public_templa te => 'n' in the DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE function defined in Step ), you need to authorize users to instantiate the dt_personnel deployment template. Use the CREATE_USER_AUTHORIZATION function in the DBMS_REPCAT_RGT package to create authorized users.

*/

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

COMMIT;

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT ***********
***********************/

Packaging a Deployment Template for Instantiation

After building your deployment template, you need to package the template for instantiation. This example illustrate s how to use both the online and offline instantiation procedures. Notice that the instantiation procedures are very similar: you sim ply use either the INSTANTIATE_ONLINE function or INSTANTIATE_OFFLINE function according to your needs. Thi s section describes two tasks: create the instantiation script and save the instantiation script to a file.

Figure 4-2 Packaging and Instantiating a Deployment Template

Tex t description of the illustration rardt2.gif

Packaging a Deployment Template

When you execute either the INSTANTIATE_OFFLINE or the INSTANTIATE_ONLINE function, Oracle populates the USER_REPCAT_TEMP_OUTPUT data dictionary view with the script to create the remote materialized view environment. Both online a nd offline scripts contain the SQL statements to create the objects specified in the deployment template. The difference is that an o ffline instantiation script also contains the data to populate the objects. The online instantiation script does not contain the data . Rather, during online instantiation, the materialized view site connects to the master site to download the data.

Complete the steps in either the "Packaging a Deployment Template for Offline Instant iation" or "Packaging a Deployment Template for Online Instantiation" according to your needs.

< a name="18073">

Note: If you need to execute either the INSTANTIATE_OFFLINE or the INSTANTIATE_ONLINE function more than o nce for a particular materialized view site, then run the DROP_SITE_INSTANTIATION procedure in the DBMS_REPCAT_RGT package before you attempt to repackage a template for the site. Otherwise, Oracle returns an error stating that there is a d uplicate template site.

Packaging a Deployment Template for Offline Instantiation

The INSTANTIATE_OFFLINE function creates a script that creates the materialized view environmen t according to the contents of a specified deployment template. In addition to containing the DDL (CREATE statements) to create the materialized view environment, this script also contains the DML (INSERT statements) to populate the materia lized view environment with the appropriate data set.

Note: If you are packaging your template at the same master site that contains the target master objects for your deployment template, then you must create a loopback database link.

--Use the INSTANTIATE_OFFLINE function to package the 
--template for offline instantiation by a remote materialized view 
--site. Executing this procedur
e both creates a script that 
--creates that materialized view environment and populates the
--
environment with the proper data set. This script is stored
--in the temporary USER_REPCAT_TEMP_OUTPUT view.

CONNECT repadmin/repadmin@orc3.world

SET SERVEROUTPUT ON
<
a name="7913">DECLARE
   dt_num NUMBER;
BEGIN
   dt_num := DBMS_REPCAT_RGT.INSTANTIATE_OFFLINE(
               refresh_template_name => 'hr_refg_dt',
               user_name => 'hr',
               site_name => 'mv4.world',
               next_date => SYSDATE,
               interval => 'SYSDATE + (1/144)');
   DBMS_OUTPUT.PUT_LINE('Template ID = ' || dt_num);
END;
/
COMMIT;
/