Oracle8i Supplied Packages Reference
Release 8.1.5

A68001-01

Library

Product

Contents

Index

Prev Next

37
DBMS_REPCAT_RGT

DBMS_REPCAT_RGT controls the maintenance and definition of refresh group templates.

Summary of Subprograms

Table 37-1 DBMS_REPCAT_RGT Package Subprograms
Subprogram  Description 
ALTER_REFRESH_TEMPLATE 
procedure
 

Lets the DBA alter existing deployment templates.  

ALTER_TEMPLATE_OBJECT 
procedure
 

Alters objects that have been added to a specified deployment template.  

ALTER_TEMPLATE_PARM 
procedure
 

Lets the DBA alter the parameters for a specific deployment template.  

ALTER_USER_AUTHORIZATION 
procedure
 

Alters the contents of the DBA_REPCAT_TEMPLATE_AUTH view.  

ALTER_USER_PARM_VALUE 
procedure
 

Changes existing parameter values that have been defined for a specific user.  

COMPARE_TEMPLATES 
function
 

Lets a DBA compare the contents of two deployment templates.  

COPY_TEMPLATE function
 

Lets the DBA copy a deployment template.  

CREATE_OBJECT_FROM_
EXISTING function
 

Creates a template object definition from existing database objects and adds it to a target deployment template.  

CREATE_REFRESH_TEMPLATE 
function
 

Creates the deployment template, which allows you to define the template name, private/public status, and target refresh group.  

CREATE_TEMPLATE_OBJECT 
function
 

Adds object definitions to a target deployment template container.  

CREATE_TEMPLATE_PARM 
function
 

Creates parameters for a specific deployment template to allow custom data sets to be created at the remote snapshot site.  

CREATE_USER_AUTHORIZATION 
function
 

Authorizes specific users to instantiate private deployment templates.  

CREATE_USER_PARM_VALUE 
function
 

Pre-defines deployment template parameter values for specific users.  

DELETE_RUNTIME_PARMS 
procedure
 

Deletes a runtime parameter value that you defined using the INSERT_RUNTIME_PARMS procedure.  

DROP_ALL_OBJECTS 
procedure
 

Lets the DBA drop all objects or specific object types from a deployment template.  

DROP_ALL_TEMPLATE_PARMS 
procedure
 

Lets the DBA drop template parameters for a specified deployment template.  

DROP_ALL_TEMPLATE_SITES 
procedure
 

Removes all entries from the DBA_REPCAT_TEMPLATE_SITES view.  

DROP_ALL_TEMPLATES 
procedure
 

Removes all deployment templates at the site where the procedure is called.  

DROP_ALL_USER_
AUTHORIZATIONS procedure
 

Lets the DBA drop all user authorizations for a specified deployment template.  

DROP_ALL_USER_PARM_VALUES 
procedure
 

Drops user parameter values for a specific deployment template.  

DROP_REFRESH_TEMPLATE 
procedure
 

Drops a deployment template.  

DROP_SITE_INSTANTIATION 
procedure
 

Removes the target site from the DBA_REPCAT_TEMPLATE_SITES view.  

DROP_TEMPLATE_OBJECT 
procedure
 

Removes a template object from a specific deployment template.  

DROP_TEMPLATE_PARM 
procedure
 

Removes an existing template parameter from the DBA_REPCAT_TEMPLATE_PARMS view.  

DROP_USER_AUTHORIZATION 
procedure
 

Removes a user authorization entry from the DBA_REPCAT_TEMPLATE_AUTH view.  

DROP_USER_PARM_VALUE 
procedure
 

Removes a pre-defined user parameter value for a specific deployment template.  

GET_RUNTIME_PARM_ID 
function
 

Retrieves an ID to be used when defining a runtime parameter value.  

INSERT_RUNTIME_PARMS 
procedure
 

Defines runtime parameter values prior to instantiating a template.  

INSTANTIATE_OFFLINE 
function
 

Generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while offline.  

INSTANTIATE_ONLINE 
function
 

Generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while online.  

LOCK_TEMPLATE_EXCLUSIVE 
procedure
 

Prevents users from reading or instantiating the template when a deployment template is being updated or modified.  

LOCK_TEMPLATE_SHARED 
procedure
 

Makes a specified deployment template read-only.  

ALTER_REFRESH_TEMPLATE procedure

This procedure lets the DBA alter existing deployment templates. Alterations may include defining a new deployment template name, a new refresh group, or a new owner and changing the public/private status.

Syntax

DBMS_REPCAT_RGT.ALTER_REFRESH_TEMPLATE (
   refresh_template_name      IN   VARCHAR2,
   new_owner                  IN   VARCHAR2 := '-',
   new_refresh_group_name     IN   VARCHAR2 := '-',
   new_refresh_template_name  IN   VARCHAR2 := '-',
   new_template_comment       IN   VARCHAR2 := '-',
   new_public_template        IN   VARCHAR2 := '-',
   new_last_modified          IN   DATE     := to_date('1', 'J'),
   new_modified_by            IN   NUMBER   := -1e-130);

Parameters

Table 37-2 ALTER_REFRESH_TEMPLATE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

The name of the deployment template that you want to alter.  

new_owner
 

The name of the new deployment template owner. Do not specify a value to keep the current owner.  

new_refresh_group_
name
 

If necessary, use this parameter to specify a new refresh group name that the template objects will be added to. Do not specify a value to keep the current refresh group.  

new_refresh_
template_name
 

Use this parameter to specify a new deployment template name. Do not specify a value to keep the current deployment template name.  

new_template_comment
 

New deployment template comments. Do not specify a value to keep the current template comment.  

new_public_template
 

Determines whether the deployment template is public or private. Only acceptable values are 'Y' and 'N' ('Y' = public and 'N' = private). Do not specify a value to keep the current value.  

new_last_modified
 

Contains the date of the last modification made to this deployment template. If a value is not specified, then the current date is automatically used.  

new_modified_by
 

Contains the name of the user who last modified this deployment template. If a value is not specified, then the current user is automatically used.  

Exceptions

Table 37-3 ALTER_REFRESH_TEMPLATE Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

bad_public_template
 

The public_template parameter is specified incorrectly. The public_template parameter must be specified as a 'Y' for a public template or an 'N' for a private template.  

dupl_refresh_
template
 

A template with the specified name already exists. See the DBA_REPCAT_REFRESH_TEMPLATES view.  

ALTER_TEMPLATE_OBJECT procedure

This procedure alters objects that have been added to a specified deployment template. The most common changes may include altering the object DDL and/or assigning the object to a different deployment template.

Changes made to the template is reflected only at new sites instantiating the deployment template. Remote sites that have already instantiated the template need to re-instantiate the deployment template to apply the changes.

Syntax

DBMS_REPCAT_RGT.ALTER_TEMPLATE_OBJECT (
   refresh_template_name       IN   VARCHAR2, 
   object_name                 IN   VARCHAR2,
   object_type                 IN   VARCHAR2,
   new_refresh_template_name   IN   VARCHAR2 := '-',
   new_object_name             IN   VARCHAR2 := '-',
   new_object_type             IN   VARCHAR2 := '-',
   new_ddl_text                IN   CLOB     := '-',
   new_master_rollback_seg     IN   VARCHAR2 := '-',
   new_flavor_id               IN   NUMBER   := -1e-130);

Parameters

Table 37-4 ALTER_TEMPLATE_OBJECT Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Deployment template name that contains the object that you want to alter.  

object_name
 

Name of the template object that you want to alter.  

object_type
 

Type of object that you want to alter.  

new_refresh_
template_name
 

Name of the new deployment template that you want to re-assign this object to. Do not specify a value to keep the object assigned to the current deployment template.  

new_object_name
 

New name of the template object. Do not specify a value to keep the current object name.  

new_object_type
 

If specified, then the new object type. Objects of the following type may be specified:

SNAPSHOT        PROCEDURE
INDEX           FUNCTION
TABLE           PACKAGE
VIEW            PACKAGE BODY
SYNONYM         TRIGGER
SEQUENCE        DATABASE LINK
 
new_ddl_text
 

New object DDL for specified object. Do not specify any new DDL text to keep the current object DDL.  

new_master_rollback_
seg
 

New master rollback segment for specified object. Do not specify a value to keep the current rollback segment.  

new_flavor_id
 

New flavor ID for the specified object. Do not specify a value to keep the current flavor ID.  

Exceptions

Table 37-5 ALTER_TEMPLATE_OBJECT Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_flavor_id
 

Flavor ID specified is invalid or does not exist.  

bad_object_type
 

Object type is specified incorrectly. See Table 37-4 for a list of valid object types.  

miss_template_object
 

Template object name specified is invalid or does not exist.  

dupl_template_object
 

New template name specified in the new_refresh_template_name parameter already exists.  

Usage Notes

Because the ALTER_TEMPLATE_OBJECT procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the ALTER_TEMPLATE_OBJECT procedure. The following example illustrates how to use the DBMS_LOB package with the ALTER_TEMPLATE_OBJECT procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'CREATE SNAPSHOT snap_sales AS SELECT *
      FROM sales WHERE salesperson = :salesid and region_id = :region';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_OBJECT(
      refresh_template_name => 'rgt_personnel',
      object_name => 'SNAP_SALES',
      object_type => 'SNAPSHOT',
      new_ddl_text => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

ALTER_TEMPLATE_PARM procedure

This procedure allows the DBA to alter the parameters for a specific deployment template. Alterations may include renaming the parameter or redefining the default value and prompt string.

Syntax

DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM (
   refresh_template_name       IN   VARCHAR2,
   parameter_name              IN   VARCHAR2,
   new_refresh_template_name   IN   VARCHAR2 := '-',
   new_parameter_name          IN   VARCHAR2 := '-',
   new_default_parm_value      IN   CLOB     := NULL,
   new_prompt_string           IN   VARCHAR2 := '-',
   new_user_override           IN   VARCHAR2 := '-');

Parameters

Table 37-6 ALTER_TEMPLATE_PARM Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the parameter that you want to alter.  

parameter_name
 

Name of the parameter that you want to alter.  

new_refresh_
template_name
 

Name of the deployment template that the specified parameter should be re-assigned to (useful when you want to move a parameter from one template to another). Do not specify a value to keep the parameter assigned to the current template.  

new_parameter_name
 

New name of the template parameter. Do not specify a value to keep the current parameter name.  

new_default_parm_
value
 

New default value for the specified parameter. Do not specify a value to keep the current default value.  

new_prompt_string
 

New prompt text for the specified parameter. Do not specify a value to keep the current prompt string.  

new_user_override
 

Determines if the user can override the default value if prompted during the instantiation process (the user is prompted if no user parameter value has been defined for this parameter). Set this parameter to 'Y' to allow a user to override the default value or set this parameter to 'N' to not allow an override.  

Exceptions

Table 37-7 ALTER_TEMPLATE_PARM Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_template_parm
 

Template parameter specified is invalid or does not exist.  

dupl_template_parm
 

Combination of new_refresh_template_name and new_parameter_name already exists.  

Usage Notes

Because the ALTER_TEMPLATE_PARM procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the ALTER_TEMPLATE_PARM procedure. The following example illustrates how to use the DBMS_LOB package with the ALTER_TEMPLATE_PARM procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_TEMPLATE_PARM(
      refresh_template_name => 'rgt_personnel',
      parameter_name => 'region',
      new_default_parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

ALTER_USER_AUTHORIZATION procedure

This procedure alters the contents of the DBA_REPCAT_TEMPLATE_AUTH view. Specifically, you can change user/deployment template authorization assignments. This procedure is helpful, for example, if an employee moves positions and requires the snapshot environment of another deployment template; the DBA simply assigns the employee the new deployment template and the user is authorized to instantiate the target template.

Syntax

DBMS_REPCAT_RGT.ALTER_USER_AUTHORIZATION (
   user_name                   IN   VARCHAR2,
   refresh_template_name       IN   VARCHAR2,
   new_user_name               IN   VARCHAR2 := '-',
   new_refresh_template_name   IN   VARCHAR2 := '-');

Parameters

Table 37-8 ALTER_USER_AUTHORIZATION Procedure Parameters
Parameter  Description 
user_name
 

Name of the user whose authorization you want to alter.  

refresh_template_
name
 

Name of the deployment template that is currently assigned to the specified user that you want to alter.  

new_user_name
 

Use this parameter to define a new user for this template authorization. Do not specify a value to keep the current user  

new_refresh_
template_name
 

The deployment template that the specified user (either the existing or, if specified, the new user) is authorized to instantiate. Do not specify a value to keep the current deployment template.  

Exceptions

Table 37-9 ALTER_USER_AUTHORIZATION Procedure Exceptions
Exception  Description 
miss_user_
authorization
 

The combination of user_name and refresh_template_name values specified does not exist in the DBA_REPCAT_TEMPLATE_AUTH view.  

miss_user
 

The user name specified for the new_user_name or user_name parameter is invalid or does not exist.  

miss_refresh_
template
 

The deployment template specified for the new_refresh_template parameter is invalid or does not exist.  

dupl_user_
authorization
 

A row already exists for the specified user name and deployment template name. See the DBA_REPCAT_AUTH_TEMPLATES view.  

ALTER_USER_PARM_VALUE procedure

This procedure changes existing parameter values that have been defined for a specific user. This procedure is especially helpful if your snapshot environment uses assignment tables; simply change a user parameter value to quickly and securely change the data set of a remote snapshot site.

See "Deployment Template Design" in the Oracle8i Replication manual for more information on using assignment tables.

Syntax

DBMS_REPCAT_RGT.ALTER_USER_PARM_VALUE(
   refresh_template_name         IN   VARCHAR2, 
   parameter_name                IN   VARCHAR2,
   user_name                     IN   VARCHAR2,
   new_refresh_template_name     IN   VARCHAR2 := '-',
   new_parameter_name            IN   VARCHAR2 := '-',
   new_user_name                 IN   VARCHAR2 := '-',
   new_parm_value                IN   CLOB     := NULL);

Parameters

Table 37-10 ALTER_USER_PARM_VALUE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the user parameter value that you want to alter.  

parameter_name
 

Name of the parameter that you want to alter.  

user_name
 

Name of the user whose parameter value you want to alter.  

new_refresh_
template_name
 

Name of the deployment template that the specified user parameter value should be re-assigned to (useful when you are authorizing a user for a different template). Do not specify a value to keep the parameter assigned to the current template.  

new_parameter_name
 

The new template parameter name. Do not specify a value to keep the user value defined for the existing parameter.  

new_user_name
 

The new user name that this parameter value is for. Do not specify a value to keep the parameter value assigned to the current user.  

new_parm_value
 

The new parameter value for the specified user parameter. Do not specify a value to keep the current parameter value.  

Exceptions

Table 37-11 ALTER_USER_PARM_VALUE Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_template_parm
 

Template parameter specified is invalid or does not exist.  

miss_user
 

User name specified for the user_name or new_user_name parameters is invalid or does not exist.  

miss_user_parm_
values
 

User parameter value specified does not exist.  

dupl_user_parm_
values
 

New user parameter specified already exists.  

Usage Notes

Because the ALTER_USER_PARM_VALUE procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the ALTER_USER_PARM_VALUE procedure. The following example illustrates how to use the DBMS_LOB package with the ALTER_USER_PARM_VALUE procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.ALTER_USER_PARM_VALUE(
      refresh_template_name => 'rgt_personnel',
      parameter_name => 'region',
      user_name => 'BOB',
      new_parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

COMPARE_TEMPLATES function

This function allows a DBA to compare the contents of two deployment templates. Any discrepancies between the two deployment templates is stored in the USER_REPCAT_TEMP_OUTPUT table.

The COMPARE_TEMPLATES function returns a number that you specify in the WHERE clause when querying the USER_REPCAT_TEMP_OUTPUT table. For example, if the COMPARE_TEMPLATES procedure returns the number 10, then you would execute the following SELECT statement to view all discrepancies between two specified templates (your SELECT statement returns no rows if the templates are identical):

SELECT text FROM USER_REPCAT_TEMP_OUTPUT
   WHERE output_id =  10 ORDER BY LINE;

The contents of the USER_REPCAT_TEMP_OUTPUT are lost after you disconnect or a ROLLBACK has been performed.

Syntax

DBMS_REPCAT_RGT.COMPARE_TEMPLATES (
   source_template_name    IN   VARCHAR2,
   compare_template_name   IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 37-12 COMPARE_TEMPLATES Function Parameters
Parameter  Description 
source_template_name
 

Name of the first deployment template to be compared.  

compare_template_
name
 

Name of the second deployment template to be compared.  

Exceptions

Table 37-13 COMPARE_TEMPLATES Function Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name to be compared is invalid or does not exist.  

Returns

Table 37-14 COMPARE_TEMPLATES Function Returns
Return Value  Description 

<system generated number>  

Specify the number returned for the output_id value when you select from the USER_REPCAT_TEMP_OUTPUT view to view the discrepancies between the compared templates.  

COPY_TEMPLATE function

This function allows the DBA to copy a deployment template. COPY_TEMPLATE is helpful when a new deployment template will use many of the object contained in an existing deployment template. This function copies the deployment template, template objects, template parameters, and user parameter values. The DBA can optionally have the function copy the user authorizations for this template. The number returned by this function is used internally by Oracle to manage deployment templates.


Note:

The values in the DBA_REPCAT_TEMPLATE_SITES view are not copied.  


This function also allows the DBA to copy a deployment template to another master site, which is helpful for deployment template distribution and to split network loads between multiple sites.

Syntax

DBMS_REPCAT_RGT.COPY_TEMPLATE (
   old_refresh_template_name     IN   VARCHAR2, 
   new_refresh_template_name     IN   VARCHAR2,
   copy_user_authorizations      IN   VARCHAR2,
   dblink                        IN   VARCHAR2 := NULL)
  RETURN NUMBER;

Parameters

Table 37-15 COPY_TEMPLATE Function Parameters
Parameter  Description 
old_refresh_
template_name
 

Name of the deployment template to be copied.  

new_refresh_
template_name
 

Name of the new deployment template.  

copy_user_
authorizations
 

Specifies whether the template authorizations for the original template should be copied for the new deployment template. Valid values for this parameter are 'Y', 'N' and NULL.

Note: All users must exist at the target database.  

dblink
 

Optionally defines where the deployment template should be copied from (this is helpful to distribute deployment templates to other master sites). If none is specified, then the deployment template is copied from the local master site.  

Exceptions

Table 37-16 COPY_TEMPLATE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name to be copied is invalid or does not exist.  

dupl_refresh_
template
 

Name of the new refresh template specified already exists.  

bad_copy_auth
 

Value specified for the copy_user_authorization parameter is invalid. Valid values are 'Y', 'N', and NULL.  

Returns

Table 37-17 COPY_TEMPLATES Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

CREATE_OBJECT_FROM_EXISTING function

This function creates a template object definition from existing database objects and adds it to a target deployment template. The object DDL that created the original database object is executed when the target deployment template is instantiated at the remote snapshot site. This is ideal for adding existing triggers and procedures to your template. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_OBJECT_FROM_EXISTING(
   refresh_template_name  IN   VARCHAR2,
   object_name            IN   VARCHAR2,
   sname                  IN   VARCHAR2,
   oname                  IN   VARCHAR2,
   otype                  IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 37-18 CREATE_OBJECT_FROM_EXISTING Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you want to add this object to.  

object_name
 

If necessary, the new name of the existing object that you are adding to your deployment template (allows you to define a new name for an existing object).  

sname
 

The schema that contains the object that you are creating your template object from.  

oname
 

Name of the object that you are creating your template object from.  

otype
 

The type of database object that you are adding to the template (i.e., PROCEDURE, TRIGGER, etc.). The object type must be specified using the following numerical identifiers (DATABASE LINK or SNAPSHOT are not a valid object types for this function):

SEQUENCE        PROCEDURE
INDEX           FUNCTION
TABLE           PACKAGE
VIEW            PACKAGE BODY
SYNONYM         TRIGGER
 

Exceptions

Table 37-19 CREATE_OBJECT_FROM_EXISTING Function Exceptions
Exception  Description 
miss_refresh_
template
 

The specified refresh template name is invalid or missing. Query the DBA_REPCAT_REFRESH_TEMPLATE view for a list of existing deployment templates.  

bad_object_type
 

The object type is specified incorrectly (see Table 37-24 for more information).  

dupl_template_object
 

An object of the same name and type has already been added to the specified deployment template.  

objectmissing
 

Existing object specified does not exist.  

Returns

Table 37-20 CREATE_OBJECT_FROM_EXISTING Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

CREATE_REFRESH_TEMPLATE function

This function creates the deployment template, which allows you to define the template name, private/public status, and target refresh group. Each time that you create a template object, user authorization, or template parameter, you reference the deployment template created with this function. This function adds a row to the DBA_REPCAT_REFRESH_TEMPLATES view. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_REFRESH_TEMPLATE (
   owner                  IN   VARCHAR2, 
   refresh_group_name     IN   VARCHAR2, 
   refresh_template_name  IN   VARCHAR2, 
   template_comment       IN   VARCHAR2 := NULL,
   public_template        IN   VARCHAR2 := NULL,
   last_modified          IN   DATE     := SYSDATE,
   modified_by            IN   VARCHAR2 := USER,
   creation_date          IN   DATE     := SYSDATE,
   created_by             IN   VARCHAR2 := USER)
  RETURN NUMBER

Parameters

Table 37-21 CREATE_REFRESH_TEMPLATE Function Parameters
Parameter  Description 
owner
 

User name of the deployment template owner is specified with this parameter. If an owner is not specified, then the name of the user creating the template is automatically used.  

refresh_group_name
 

Name of the refresh group that is created when this template is instantiated. All objects created by this template are assigned to the specified refresh group.  

refresh_template_
name
 

Name of the deployment template that you are creating. This name is referenced in all activities that involve this deployment template.  

template_comment
 

User comments defined with this are listed in the DBA_REPCAT_REFRESH_TEMPLATES view.  

public_template
 

Specifies whether the deployment template is public or private. Only acceptable values are 'Y' and 'N' ('Y' = public and 'N' = private).  

last_modified
 

The date of the last modification made to this deployment template. If a value is not specified, then the current date is automatically used.  

modified_by
 

Name of the user who last modified this deployment template. If a value is not specified, then the current user is automatically used.  

creation_date
 

The date that this deployment template was created. If a value is not specified, then the current date is automatically used.  

created_by
 

Name of the user who created this deployment template. If a value is not specified, then the current user is automatically used.  

Exceptions

Table 37-22 CREATE_REFRESH_TEMPLATE Function Exceptions
Exception  Description 
dupl_refresh_
template
 

A template with the specified name already exists. See the DBA_REPCAT_REFRESH_TEMPLATES view to see a list of existing templates.  

bad_public_template
 

The public_template parameter is specified incorrectly. The public_template parameter must be specified as a 'Y' for a public template or an 'N' for a private template.  

Returns

Table 37-23 CREATE_REFRESH_TEMPLATE Function Returns
Return Value  Description 
<system generated 
number>
 

System generated number is used internally by Oracle.  

CREATE_TEMPLATE_OBJECT function

This function adds object definitions to a target deployment template container. The specified object DDL is executed when the target deployment template is instantiated at the remote snapshot site. In addition to adding snapshots, this function can add tables, procedures, and other objects to your template. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
   refresh_template_name  IN   VARCHAR2, 
   object_name            IN   VARCHAR2, 
   object_type            IN   VARCHAR2,
   ddl_text               IN   CLOB,
   master_rollback_seg    IN   VARCHAR2 := NULL,
   flavor_id              IN   NUMBER   := -1e-130)
  RETURN NUMBER;

Parameters

Table 37-24 CREATE_TEMPLATE_OBJECT Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you want to add this object to.  

object_name
 

Name of the template object that you are creating.  

object_type
 

The type of database object that you are adding to the template (i.e., SNAPSHOT, TRIGGER, PROCEDURE, etc.). Objects of the following type may be specified:

SNAPSHOT        PROCEDURE
INDEX           FUNCTION
TABLE           PACKAGE
VIEW            PACKAGE BODY
SYNONYM         MATERIALIZED VIEW
SEQUENCE        DATABASE LINK
TRIGGER
 
ddl_text
 

Contains the DDL that creates the object that you are adding to the template. Be sure to end your DDL with a semi-colon. (Remember, you can use a colon (:) to create a template parameter for your template object; see "Creating Snapshots with Deployment Templates" in the Oracle8i Replication book for more information.  

master_rollback_seg
 

Specifies the name of the rollback segment to use when executing the defined object DDL at the remote snapshot site.  

flavor_id
 

Defines the flavor ID for this template object.  

Exceptions

Table 37-25 CREATE_TEMPLATE_OBJECT Function Exceptions
Exception  Description 
miss_refresh_
template
 

Specified refresh template name is invalid or missing. Query the DBA_REPCAT_REFRESH_TEMPLATE view for a list of existing deployment templates.  

bad_object_type
 

Object type is specified incorrectly. See Table 37-24 for a list of valid object types.  

dupl_template_object
 

An object of the same name and type has already been added to the specified deployment template.  

Returns

Table 37-26 CREATE_TEMPLATE_OBJECT Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

Usage Notes

Because CREATE_TEMPLATE_OBJECT utilizes a CLOB, you need to utilize the DBMS_LOB package when using the CREATE_TEMPLATE_OBJECT function. The following example illustrates how to use the DBMS_LOB package with the CREATE_TEMPLATE_OBJECT function:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'CREATE SNAPSHOT snap_sales AS SELECT *
        FROM sales WHERE salesperson = :salesid';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT(
        refresh_template_name => 'rgt_personnel',
        object_name => 'snap_sales',
        object_type => 'SNAPSHOT',
        ddl_text => templob,
        master_rollback_seg => 'RBS');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

CREATE_TEMPLATE_PARM function

This function creates parameters for a specific deployment template to allow custom data sets to be created at the remote snapshot site. This function is only required when the DBA wants to define a set of template variables before adding any template objects (when objects are added to the template using the CREATE_TEMPLATE_OBJECT function, any variables in the object DDL are automatically added to the DBA_REPCAT_TEMPLATE_PARMS view).

The DBA typically uses the ALTER_TEMPLATE_PARM function to modify the default parameter values and/or prompt strings (see ALTER_TEMPLATE_PARM procedure for more information). The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_TEMPLATE_PARM (
   refresh_template_name  IN   VARCHAR2, 
   parameter_name         IN   VARCHAR2,
   default_parm_value     IN   CLOB     := NULL,
   prompt_string          IN   VARCHAR2 := NULL,
   user_override          IN   VARCHAR2 := NULL)
  RETURN NUMBER;

Parameters

Table 37-27 CREATE_TEMPLATE_PARM Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you want to create the parameter for.  

parameter_name
 

Name of the parameter you are creating.  

default_parm_value
 

Default values for this parameter are defined using this parameter. If a user parameter value or runtime parameter value is not present, then this default value is used during the instantiation process.  

prompt_string
 

The descriptive prompt text that is displayed for this template parameter during the instantiation process.  

user_override
 

Determines if the user can override the default value if prompted during the instantiation process (the user is prompted if no user parameter value has been defined for this parameter). Set this parameter to 'Y' to allow a user to override the default value or set this parameter to 'N' to not allow an override.  

Exceptions

Table 37-28 CREATE_TEMPLATE_PARM Function Exceptions
Exception  Description 
miss_refresh_
template
 

The specified refresh template name is invalid or missing.  

dupl_template_parm
 

A parameter of the same name has already been defined for the specified deployment template.  

Returns

Table 37-29 CREATE_TEMPLATE_PARM Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

Usage Notes

Because the CREATE_TEMPLATE_PARM function utilizes a CLOB, you need to utilize the DBMS_LOB package when using the CREATE_TEMPLATE_PARM function. The following example illustrates how to use the DBMS_LOB package with the CREATE_TEMPLATE_PARM function:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_PARM(
        refresh_template_name => 'rgt_personnel',
        parameter_name => 'region',
        default_parm_value => templob,
        prompt_string => 'Enter your region ID:',
        user_override => 'Y');
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

CREATE_USER_AUTHORIZATION function

This function authorizes specific users to instantiate private deployment templates. Users not authorized for a private deployment template are not able to instantiate the private template. This function adds a row to the DBA_REPCAT_AUTH_TEMPLATES view.

Before you authorize a user, verify that the user exists at the master site where the user will instantiate the deployment template. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_USER_AUTHORIZATION (
   user_name               IN   VARCHAR2, 
   refresh_template_name   IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 37-30 CREATE_USER_AUTHORIZATION Function Parameters
Parameter  Description 
user_name
 

Name of the user that you want to authorize to instantiate the specified template. Specify multiple users by separating user names with a comma (i.e., 'john, mike, bob')  

refresh_template_
name
 

Name of the template that you want to authorize the specified user to instantiate.  

Exceptions

Table 37-31 CREATE_USER_AUTHORIZATION Function Exceptions
Exception  Description 
miss_user
 

User name supplied is invalid or does not exist.  

miss_refresh_
template
 

Refresh template name supplied is invalid or does not exist.  

dupl_user_
authorization
 

An authorization has already been created for the specified user and deployment template. See the DBA_REPCAT_AUTH_TEMPLATES view for a listing of template authorizations.  

Returns

Table 37-32 CREATE_USER_AUTHORIZATION Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

CREATE_USER_PARM_VALUE function

This function is used to pre-define deployment template parameter values for specific users. For example, if you want to pre-defined the region parameter as WEST for user 33456, then you would use the this function.

Any values specified with this function take precedence over default values specified for the template parameter. The number returned by this function is used internally by Oracle to manage deployment templates.

Syntax

DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE (
   refresh_template_name    IN   VARCHAR2, 
   parameter_name           IN   VARCHAR2,
   user_name                IN   VARCHAR2,
   parm_value               IN   CLOB := NULL)
  RETURN NUMBER;

Parameters

Table 37-33 CREATE_USER_PARM_VALUE Function Parameters
Parameter  Description 
refresh_template_
name
 

Specifies the name of the deployment template that contains the parameter you are creating a user value for.  

parameter_name
 

Name of the template parameter that you are defining a user parameter value for.  

user_name
 

Specifies the name of the user that you are pre-defining a parameter value for.  

parm_value
 

The pre-defined parameter value that will be used during the instantiation process initiated by the specified user.  

Exceptions

Table 37-34 CREATE_USER_PARM_VALUE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or missing.  

dupl_user_parm_
values
 

A parameter value for the specified user, parameter, and deployment template has already been defined. Query the DBA_REPCAT_USER_PARMS view for a listing of existing user parameter values.  

miss_template_parm
 

Specified deployment template parameter name is invalid or missing.  

miss_user
 

Specified user name is invalid or missing.  

Returns

Table 37-35 CREATE_USER_PARM_VALUE Function Returns
Return Value  Description 

<system generated number>  

System generated number is used internally by Oracle.  

Usage Notes

Because the CREATE_USER_PARM_VALUE function utilizes a CLOB, you need to utilize the DBMS_LOB package when using the this function. The following example illustrates how to use the DBMS_LOB package with the CREATE_USER_PARM_VALUE function:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
   a NUMBER;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   a := DBMS_REPCAT_RGT.CREATE_USER_PARM_VALUE(
        refresh_template_name => 'rgt_personnel',
        parameter_name => 'region',
        user_name => 'BOB',
        user_parm_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

DELETE_RUNTIME_PARMS procedure

Use this procedure before instantiating a deployment template to delete a runtime parameter value that you defined using the INSERT_RUNTIME_PARMS procedure.

Syntax

DBMS_REPCAT_RGT.DELETE_RUNTIME_PARMS(
   runtime_parm_id    IN   NUMBER, 
   parameter_name     IN   VARCHAR2);

Parameters

Table 37-36 DELETE_RUNTIME_PARMS Procedure Parameters
Parameter  Description 
runtime_parm_id
 

Specifies the ID that you previously assigned the runtime parameter value to (this value was retrieved using the GET_RUNTIME_PARM_ID function).  

parameter_name
 

Specifies the name of the parameter value that you want to drop (query the DBA_REPCAT_TEMPLATE_PARMS for a list of deployment template parameters).  

Exceptions

Table 37-37 DELETE_RUNTIME_PARMS Procedure Exceptions
Exception  Description 
miss_template_parm
 

The specified deployment template parameter name is invalid or missing.  

DROP_ALL_OBJECTS procedure

This procedure allows the DBA to drop all objects or specific object types from a deployment template.


Caution:

This is a dangerous procedure that cannot be undone.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_OBJECTS (
   refresh_template_name   IN   VARCHAR2,
   object_type             IN   VARCHAR2 := NULL);

Parameters

Table 37-38 DROP_ALL_OBJECTS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the objects that you want to drop.  

object_type
 

If NULL, then all objects in the template are dropped. If an object type is specified, then only objects of that type are dropped. Objects of the following type may be specified:

SNAPSHOT        PROCEDURE
INDEX           FUNCTION
TABLE           PACKAGE
VIEW            PACKAGE BODY
SYNONYM         MATERIALIZED VIEW
SEQUENCE        DATABASE LINK
TRIGGER
 

Exceptions

Table 37-39 DROP_ALL_OBJECTS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

bad_object_type
 

Object type is specified incorrectly. See Table 37-38 for a list of valid object types.  

DROP_ALL_TEMPLATE_PARMS procedure

This procedure allows the DBA to drop template parameters for a specified deployment template. The DBA can use this procedure to drop all parameters that are not referenced by a template objects or drop all objects that reference a parameter and the parameters themselves.


Caution:

This is a dangerous procedure that cannot be undone.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_TEMPLATE_PARMS (
   refresh_template_name   IN   VARCHAR2,
   drop_objects            IN   VARCHAR2 := N);

Parameters

Table 37-40 DROP_ALL_TEMPLATE_PARMS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the parameters that you want to drop.  

drop_objects
 

If no value is specified, then this defaults to N, which drops all parameters not referenced by a template object.

If Y is specified, then all objects that reference a template parameter and the template parameters themselves are dropped.  

Exceptions

Table 37-41 DROP_ALL_TEMPLATE_PARMS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_ALL_TEMPLATE_SITES procedure

This procedure removes all entries from the DBA_REPCAT_TEMPLATE_SITES view, which keeps a record of sites that have instantiated a particular deployment template.


Caution:

This is a dangerous procedure that cannot be undone. Additionally, Oracle Lite sites that have instantiated the dropped template will no longer be able to refresh their snapshots.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_TEMPLATE_SITES (
   refresh_template_name   IN   VARCHAR2);

Parameters

Table 37-42 DROP_ALL_TEMPLATE_SITES Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the sites that you want to drop.  

Exceptions

Table 37-43 DROP_ALL_TEMPLATE_SITES Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_ALL_TEMPLATES procedure

This procedure removes all deployment templates at the site where the procedure is called.


Caution:

This is a dangerous procedure that cannot be undone.  


Syntax

DBMS_REPCAT_RGT.DROP_ALL_TEMPLATES;

Parameters

None.

DROP_ALL_USER_AUTHORIZATIONS procedure

This procedure allows the DBA to drop all user authorizations for a specified deployment template. Executing this procedure removes rows from the DBA_REPCAT_AUTH_TEMPLATES view.

This procedure might be implemented after converting a private template to a public template and the user authorizations are no longer required.

Syntax

DBMS_REPCAT_RGT.DROP_ALL_USER_AUTHORIZATIONS (
   refresh_template_name   IN   VARCHAR2);

Parameters

Table 37-44 DROP_ALL_USER_AUTHORIZATIONS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the objects that you want to drop.  

Exceptions

Table 37-45 DROP_ALL_USER_AUTHORIZATIONS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_ALL_USER_PARM_VALUES procedure

This procedure drops user parameter values for a specific deployment template. This procedure is very flexible in allowing the DBA to define a set of user parameter values to be deleted. For example, defining the following parameters have the effect:

refresh_template_name: drops all user parameters for the specified deployment template.

refresh_template_name, user_name: drops all of the specified user parameters for the specified deployment template.

refresh_template_name, parameter_name: drops all user parameter values for the specified deployment template parameter.

refresh_template_name, parameter_name, user_name: drops the specified user's value for the specified deployment template parameter (equivalent to DROP_USER_PARM).

Syntax

DBMS_REPCAT_RGT.DROP_ALL_USER_PARMS (
   refresh_template_name   IN   VARCHAR2,
   user_name               IN   VARCHAR2,
   parameter_name          IN   VARCHAR2);

Parameters

Table 37-46 DROP_ALL_USER_PARMS Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that contains the parameter values that you want to drop.  

user_name
 

Name of the user whose parameter values you want to drop.  

parameter_name
 

Template parameter that contains the values that you want to drop.  

Exceptions

Table 37-47 DROP_ALL_USER_PARMS Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_user
 

User name specified is invalid or does not exist.  

miss_user_parm_
values
 

Deployment template, user, and parameter combination does not exist in the DBA_REPCAT_USER_PARM view.  

DROP_REFRESH_TEMPLATE procedure

This procedure drops a deployment template. Dropping a deployment template has a cascading effect, removing all related template parameters, user authorizations, template objects, and user parameters (this procedure does not drop template sites).

Syntax

DBMS_REPCAT_RGT.DROP_REFRESH_TEMPLATE (
   refresh_template_name  IN   VARCHAR2);

Parameters

Table 37-48 DROP_REFRESH_TEMPLATE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template to be dropped.  

Exceptions

Table 37-49 DROP_REFRESH_TEMPLATE Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name specified is invalid or does not exist. Query the DBA_REPCAT_REFRESH_TEMPLATE view for a list of deployment templates.  

DROP_SITE_INSTANTIATION procedure

Purpose

This procedure drops a template instantiation at a target site. This procedure removes all related meta data at the master site and disables the specified site from refreshing their snapshots.

Syntax

DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION(
   refresh_template_name  IN   VARCHAR2,
   user_name              IN   VARCHAR2,
   site_name              IN   VARCHAR2,
   repapi_site_id         IN   NUMBER := -1e-130);

Parameters

Table 37-50 Parameter for DROP_SITE_INSTANTIATION
Parameter  Description 
refresh_template_name
 

The name of the deployment template to be dropped.  

user_name
 

Enter the name of the user that originally instantiated the template at the remote snapshot site. Query the REPCAT_TEMPLATE_SITES view to see the users that instantiated templates.  

site_name
 

Identifies the Oracle server site where you want to drop the specified template instantiation. (If you specify a site_name, then do not specify a repapi_site_id).  

repapi_site_id
 

Identifies the REPAPI location where you want to drop the specified template instantiation. (If you specify a repapi_site_id, then do not specify a site_name).  

DROP_TEMPLATE_OBJECT procedure

This procedure removes a template object from a specific deployment template. For example, a DBA would use this procedure to remove an outdated snapshot from a deployment template. Changes made to the template are reflected at new sites instantiating the deployment template. Remote sites that have already instantiated the template need to re-instantiate the deployment template to apply the changes.

Syntax

DBMS_REPCAT_RGT.DROP_TEMPLATE_OBJECT (
   refresh_template_name  IN   VARCHAR2, 
   object_name            IN   VARCHAR2,
   object_type            IN   VARCHAR2);

Parameters

Table 37-51 DROP_TEMPLATE_OBJECT Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that you are dropping the object from.  

object_name
 

Name of the template object to be dropped.  

object_type
 

The type of object that is to be dropped. Objects of the following type may be specified:

SNAPSHOT        PROCEDURE
INDEX           FUNCTION
TABLE           PACKAGE
VIEW            PACKAGE BODY
SYNONYM         MATERIALIZED VIEW
SEQUENCE        DATABASE LINK
TRIGGER
 

Exceptions

Table 37-52 DROP_TEMPLATE_OBJECT Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name specified is invalid or does not exist.  

miss_template_object
 

The template object specified is invalid or does not exist. Query the DBA_REPCAT_TEMPLATE_OBJECT view to see a list of deployment template objects.  

DROP_TEMPLATE_PARM procedure

This procedure removes an existing template parameter from the DBA_REPCAT_TEMPLATE_PARMS view. This procedure is helpful when you have dropped a template object and a particular parameter is no longer needed.

Syntax

DBMS_REPCAT_RGT.DROP_TEMPLATE_PARM (
   refresh_template_name  IN   VARCHAR2, 
   parameter_name         IN   VARCHAR2);

Parameters

Table 37-53 DROP_TEMPLATE_PARM Procedure Parameters
Parameter  Description 
refresh_template_
name
 

The deployment template name that has the parameter that you want to drop  

parameter_name
 

Name of the parameter that you want to drop.  

Exceptions

Table 37-54 DROP_TEMPLATE_PARM Procedure Exceptions
Exception  Description 
miss_refresh_
template
 

The deployment template name specified is invalid or does not exist.  

miss_template_parm
 

The parameter name specified is invalid or does not exist. Query the DBA_REPCAT_TEMPLATE_PARMS view to see a list of template parameters.  

DROP_USER_AUTHORIZATION procedure

This procedure removes a user authorization entry from the DBA_REPCAT_TEMPLATE_AUTH view. This procedure is used when removing a user's template authorization. If a user's authorization is removed, then the user is no longer able to instantiate the target deployment template.

See Also:

DROP_ALL_USER_AUTHORIZATIONS procedure.  

Syntax

DBMS_REPCAT_RGT.DROP_USER_AUTHORIZATION (
   refresh_template_name   IN   VARCHAR2,
   user_name               IN   VARCHAR2);

Parameters

Table 37-55 DROP_USER_AUTHORIZATION Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template that the user's authorization is being removed from.  

user_name
 

Name of the user whose authorization is being removed.  

Exceptions

Table 37-56 DROP_USER_AUTHORIZATION Procedure Exceptions
Exception  Description 
miss_user
 

Specified user name is invalid or does not exist.  

miss_user_
authorization
 

Specified user and deployment template combination does not exist. Query the DBA_REPCAT_TEMPLATE_AUTH view to see a list of user/deployment template authorizations.  

miss_refresh_
template
 

Specified deployment template name is invalid or does not exist.  

DROP_USER_PARM_VALUE procedure

This procedure removes a pre-defined user parameter value for a specific deployment template. This procedure is often executed after a user's template authorization has been removed.

Syntax

DBMS_REPCAT_RGT.DROP_USER_PARM_VALUE (
   refresh_template_name    IN   VARCHAR2, 
   parameter_name           IN   VARCHAR2,
   user_name                IN   VARCHAR2);

Parameters

Table 37-57 DROP_USER_PARM_VALUE Procedure Parameters
Parameter  Description 
refresh_template_
name
 

Deployment template name that contains the parameter value that you want to drop.  

parameter_name
 

Parameter name that contains the pre-defined value that you want to drop.  

user_name
 

Name of the user whose parameter value you want to drop.  

Exceptions

Table 37-58 DROP_USER_PARM_VALUE Procedure Exceptions
Exception  Description 

miss_refresh_template  

Deployment template name specified is invalid or does not exist.  

miss_user  

User name specified is invalid or does not exist.  

miss_user_parm_values  

Deployment template, user, and parameter combination does not exist in the DBA_REPCAT_USER_PARM view.  

GET_RUNTIME_PARM_ID function

This function retrieves an ID to be used when defining a runtime parameter value. All runtime parameter values are assigned to this ID and are also used during the instantiation process.

Syntax

DBMS_REPCAT_RGT.GET_RUNTIME_PARM_ID
  RETURN NUMBER;

Parameters

None.

Returns

Table 37-59 GET_RUNTIME_PARM_ID Function Returns
Return Value  Corresponding Datatype 

<system generated number>  

Runtime parameter values are assigned to the system generated number and is also used during the instantiation process.  

INSERT_RUNTIME_PARMS procedure

This procedure defines runtime parameter values prior to instantiating a template. This procedure should be used to define parameter values when no user parameter values have been defined and you do not want to accept the default parameter values.

Before using the this procedure, be sure to execute the GET_RUNTIME_PARM_ID function to retrieve a parameter ID to be used when inserting a runtime parameter. This ID is used for defining runtime parameter values and instantiating deployment template.

Syntax

DBMS_REPCAT_RGT.INSERT_RUNTIME_PARMS (
   runtime_parm_id    IN   NUMBER, 
   parameter_name     IN   VARCHAR2,
   parameter_value    IN   CLOB);

Parameters

Table 37-60 INSERT_RUNTIME_PARMS Procedure Parameters
Parameter  Description 
runtime_parm_id
 

The ID retrieved by the GET_RUNTIME_PARM_ID function. This ID is also used when instantiating the deployment template (be sure to use the same ID for all parameter values for a deployment template).  

parameter_name
 

Name of the template parameter that you are defining a runtime parameter value for (query the DBA_REPCAT_TEMPLATE_PARMS view for a list of template parameters).  

parameter_value
 

The runtime parameter value that you want to use during the deployment template instantiation process.  

Exceptions

Table 37-61 INSERT_RUNTIME_PARMS Procedure Exceptions
Exception  Description 

miss_refresh_template  

The deployment template name specified is invalid or does not exist.  

miss_user  

The user name specified is invalid or does not exist.  

miss_user_parm_values  

The deployment template, user, and parameter combination does not exist in the DBA_REPCAT_USER_PARM view.  

Usage Notes

Because the this procedure utilizes a CLOB, you need to utilize the DBMS_LOB package when using the INSERT_RUNTIME_PARMS procedure. The following example illustrates how to use the DBMS_LOB package with the INSERT_RUNTIME_PARMS procedure:

DECLARE
   tempstring VARCHAR2(100);
   templob CLOB;
BEGIN
   DBMS_LOB.CREATETEMPORARY(templob, TRUE, DBMS_LOB.SESSION);
   tempstring := 'REGION 20';
   DBMS_LOB.WRITE(templob, length(tempstring), 1, tempstring);
   DBMS_REPCAT_RGT.INSERT_RUNTIME_PARMS(
      runtime_parm_id => 20,
      parameter_name => 'region',
      parameter_value => templob);
   DBMS_LOB.FREETEMPORARY(templob);
END;
/

INSTANTIATE_OFFLINE function

This function generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while offline. This generated script should be used at remote snapshot sites that are able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote snapshot site may be lengthy (depending on the amount of data that is populated to the new snapshots). This procedure needs to be executed separately for each user instantiation.

The script generated by this function is stored in the USER_REPCAT_TEMP_OUTPUT temporary view and is used by several Oracle tools, including Replication Manager, during the distribution of deployment templates. The number returned by this function is used to retrieve the appropriate information from the USER_REPCAT_TEMP_OUTPUT view.


Note:

This procedure is used in performing an offline instantiation of a deployment template. Additionally, this procedure is for replication administrators that are instantiating for another user. Users wanting to perform their own instantiation should use the public version of the "INSTANTIATE_OFFLINE function".

This procedure should not be confused with the procedures in the DBMS_OFFLINE_OG package (used for performing an offline instantiation of a master table) or with the procedures in the DBMS_OFFLINE_SNAPSHOT package (used for performing an offline instantiation of a snapshot). See these respective packages for more information on their usage.  


Syntax

DBMS_REPCAT_RGT.INSTANTIATE_OFFLINE(
     refresh_template_name   IN   VARCHAR2,
     site_name               IN   VARCHAR2, 
     user_name               IN   VARCHAR2 := NULL,
     runtime_parm_id         IN   NUMBER   := -1e-130,
     next_date               IN   DATE     := SYSDATE,
     interval                IN   VARCHAR2 :'SYSDATE + 1')
    RETURN NUMBER;

Parameters

Table 37-62 INSTANTIATE_OFFLINE Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template to be instantiated.  

site_name
 

Name of the remote site that is instantiating the deployment template.  

user_name
 

Name of the authorized user that is instantiating the deployment template.  

runtime_parm_id
 

If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, then specify the ID used when creating the runtime parameters (the ID was retrieved by using the GET_RUNTIME_PARM_ID function).  

next_date
 

Specifies the next refresh date value to be used when creating the refresh group.  

interval
 

Specifies the refresh interval to be used when creating the refresh group.  

Exceptions

Table 37-63 INSTANTIATE_OFFLINE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_user
 

Name of the authorized user is invalid or does not exist. Verify that the specified user is listed in the DBA_REPCAT_TEMPLATE_AUTH view; if user is not listed, then the specified user is not authorized to instantiate the target deployment template.  

bad_parms
 

All of the template parameters were not populated by the defined user parameter values and/or template default values. The number of pre-defined values may not have matched the number of template parameters or pre-defined value was invalid for the target parameter (i.e., type mismatch).  

Returns

Table 37-64 INSTANTIATE_OFFLINE Function Returns
Return Value  Description 

<system generated number>  

Specify the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated instantiation script.  

INSTANTIATE_ONLINE function

This function generates a script at the master site that is used to create the snapshot environment at the remote snapshot site while online. This generated script should be used at remote snapshot sites that are able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote snapshot site may be lengthy (depending on the amount of data that is populated to the new snapshots). This procedure needs to be executed separately for each user instantiation.

The script generated by this function is stored in the USER_REPCAT_TEMP_OUTPUT temporary view and is used by several Oracle tools, including Replication Manager, during the distribution of deployment templates. The number returned by this function is used to retrieve the appropriate information from the USER_REPCAT_TEMP_OUTPUT view.


Note:

This procedure is for replication administrators that are instantiating for another user. Users wanting to perform their own instantiation should use the public version of the "INSTANTIATE_OFFLINE function".  


Syntax

DBMS_REPCAT_RGT.INSTANTIATE_ONLINE(
   refresh_template_name   IN   VARCHAR2,
   site_name               IN   VARCHAR2 := NULL,
   user_name               IN   VARCHAR2 := NULL, 
   runtime_parm_id         IN   NUMBER   := -1e-130,
   next_date               IN   DATE     := SYSDATE,
   interval                IN   VARCHAR2 :'SYSDATe + 1')
  RETURN NUMBER;

Parameters

Table 37-65 INSTANTIATE_ONLINE Function Parameters
Parameter  Description 
refresh_template_
name
 

Name of the deployment template to be instantiated.  

site_name
 

Name of the remote site that is instantiating the deployment template.  

user_name
 

Name of the authorized user that is instantiating the deployment template.  

runtime_parm_id
 

If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, then specify the ID used when creating the runtime parameters (the ID was retrieved by using the GET_RUNTIME_PARM_ID function).  

next_date
 

Specifies the next refresh date value to be used when creating the refresh group.  

interval
 

Specifies the refresh interval to be used when creating the refresh group.  

Exceptions

Table 37-66 INSTANTIATE_ONLINE Function Exceptions
Exception  Description 
miss_refresh_
template
 

Deployment template name specified is invalid or does not exist.  

miss_user
 

Name of the authorized user is invalid or does not exist. Verify that the specified user is listed in the DBA_REPCAT_TEMPLATE_AUTH view; if user is not listed, then the specified user is not authorized to instantiate the target deployment template.  

bad_parms
 

All of the template parameters were not populated by the defined user parameter values and/or template default values. The number of pre-defined values may not have matched the number of template parameters or pre-defined value was invalid for the target parameter (i.e., type mismatch).  

Returns

Table 37-67 INSTANTIATE_ONLINE Function Returns
Return Value  Description 

<system generated number>  

Specify the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT view to retrieve the generated instantiation script.  

LOCK_TEMPLATE_EXCLUSIVE procedure

When a deployment template is being updated or modified, you should use the LOCK_TEMPLATE_EXCLUSIVE procedure to prevent users from reading or instantiating the template.

The lock will be released when a ROLLBACK or COMMIT is performed.


Note:

This procedure should be executed before you make any modifications to your deployment template.  


Syntax

DBMS_REPCAT_RGT.LOCK_TEMPLATE_EXCLUSIVE;

Parameters

None.

LOCK_TEMPLATE_SHARED procedure

This procedure is used to make a specified deployment template "read-only." This procedure should be called before instantiating a template, as this will ensure that nobody can change the deployment template while it is being instantiated.

The lock will be released when a ROLLBACK or COMMIT is performed.

Syntax

DBMS_REPCAT_RGT.LOCK_TEMPLATE_SHARED;

Parameters

None.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index