Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
|
|
View PDF |
DBMS_REPCAT_RGT
controls the maintenance and definition of refresh group templates.
This chapter contains this topic:
Table 21-1 DBMS_REPCAT_RGT Package Subprograms
Subprogram | Description |
---|---|
"ALTER_REFRESH_TEMPLATE Procedure" |
Allows the DBA to alter existing deployment templates. |
"ALTER_TEMPLATE_OBJECT Procedure" |
Alters objects that have been added to a specified deployment template. |
"ALTER_TEMPLATE_PARM Procedure" |
Allows the DBA to alter the parameters for a specific deployment template. |
"ALTER_USER_AUTHORIZATION Procedure" |
Alters the contents of the DBA_REPCAT_USER_AUTHORIZATIONS view. |
"ALTER_USER_PARM_VALUE Procedure" |
Changes existing parameter values that have been defined for a specific user. |
"COMPARE_TEMPLATES Function" |
Allows the DBA to compare the contents of two deployment templates. |
"COPY_TEMPLATE Function" |
Allows the DBA to 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 the DBA to define the template name, private/public status, and target refresh group. |
"CREATE_TEMPLATE_OBJECTFunction" |
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 materialized view site. |
"CREATE_USER_AUTHORIZATION Function" |
Authorizes specific users to instantiate private deployment templates. |
"CREATE_USER_PARM_VALUE Function" |
Predefines 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" |
Allows the DBA to drop all objects or specific object types from a deployment template. |
"DROP_ALL_TEMPLATE_PARMS Procedure" |
Allows the DBA to 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" |
Allows the DBA to 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_USER_AUTHORIZATIONS view. |
"DROP_USER_PARM_VALUE Procedure" |
Removes a predefined user parameter value for a specific deployment template. |
"GET_RUNTIME_PARM_ID Function" |
Retrieves an identification 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 materialized view environment at the remote materialized view site while offline. |
"INSTANTIATE_ONLINE Function" |
Generates a script at the master site that is used to create the materialized view environment at the remote materialized view 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. |
This procedure allows the DBA to alter existing deployment templates. Alterations can 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 21-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 to which the template objects will be added. 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 21-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 ALL_REPCAT_REFRESH_TEMPLATES view. |
This procedure alters objects that have been added to a specified deployment template. The most common changes are altering the object DDL and assigning the object to a different deployment template.
Changes made to the template are reflected only at new sites instantiating the deployment template. Remote sites that have already instantiated the template must reinstantiate 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 21-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 to which you want to reassign this object. 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 can be specified:
MATERIALIZED VIEW 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 |
This parameter is for internal use only.
Note: Do not set this parameter unless directed to do so by Oracle Support Services. |
Exceptions
Table 21-5 ALTER_TEMPLATE_OBJECT Procedure Exceptions
Exception | Description |
---|---|
miss_refresh_template |
Deployment template name specified is invalid or does not exist. |
miss_flavor_id |
If you receive this exception, contact Oracle Support Services. |
bad_object_type |
Object type is specified incorrectly. See Table 21-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 must use 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 MATERIALIZED VIEW mview_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 => 'MVIEW_SALES', object_type => 'MATERIALIZED VIEW', new_ddl_text => templob); DBMS_LOB.FREETEMPORARY(templob); END; /
This procedure allows the DBA to alter the parameters for a specific deployment template. Alterations include renaming the parameter and 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 21-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 reassigned 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 whether 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 prevent an override. |
Exceptions
Table 21-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 must use 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; /
This procedure alters the contents of the DBA_REPCAT_USER_AUTHORIZATIONS
view. Specifically, you can change user/deployment template authorization assignments. This procedure is helpful, for example, if an employee is reassigned and requires the materialized view 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 21-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 21-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_USER_AUTHORIZATIONS 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 ALL_REPCAT_USER_AUTHORIZATIONS view. |
This procedure changes existing parameter values that have been defined for a specific user. This procedure is especially helpful if your materialized view environment uses assignment tables. Change a user parameter value to quickly and securely change the data set of a remote materialized view site.
See Also:
Oracle Database Advanced Replication for more information on using assignment tablesSyntax
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 21-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 reassigned 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 21-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 must use 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; /
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
temporary view.
The COMPARE_TEMPLATES
function returns a number that you specify in the WHERE
clause when querying the USER_REPCAT_TEMP_OUTPUT
temporary view. For example, if the COMPARE_TEMPLATES
procedure returns the number 10, 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
temporary view 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 21-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 21-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 21-14 COMPARE_TEMPLATES Function Returns
Return Value | Description |
---|---|
<system-generated number> | Specifies the number returned for the output_id value when you select from the USER_REPCAT_TEMP_OUTPUT temporary view to view the discrepancies between the compared templates. |
This function enables you to copy a deployment template and is helpful when a new deployment template uses many of the objects 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 theDBA_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 21-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 21-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 21-17 COPY_TEMPLATES Function Returns
Return Value | Description |
---|---|
<system-generated number> | System-generated number used internally by Oracle. |
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 materialized view 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 21-18 CREATE_OBJECT_FROM_EXISTING Function Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template to which you want to add this object. |
object_name |
Optionally, the new name of the existing object that you are adding to your deployment template (enables 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 (that is, PROCEDURE , TRIGGER , and so on). Objects of the following types can be specified (DATABASE LINK , MATERIALIZED VIEW , and MATERIALIZED VIEW are not valid object types for this function):
SEQUENCE PROCEDURE INDEX FUNCTION TABLE PACKAGE VIEW PACKAGE BODY SYNONYM TRIGGER |
Exceptions
Table 21-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_TEMPLATES view for a list of existing deployment templates. |
bad_object_type |
The object type is specified incorrectly. |
dupl_template_object |
An object of the same name and type has already been added to the specified deployment template. |
objectmissing |
The object specified does not exist. |
Returns
Table 21-20 CREATE_OBJECT_FROM_EXISTING Function Returns
Return Value | Description |
---|---|
<system-generated number> | System-generated number used internally by Oracle. |
This function creates the deployment template, which enables 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 21-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 parameter 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 21-22 CREATE_REFRESH_TEMPLATE Function Exceptions
Exception | Description |
---|---|
dupl_refresh_template |
A template with the specified name already exists. See the ALL_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 21-23 CREATE_REFRESH_TEMPLATE Function Returns
Return Value | Description |
---|---|
<system-generated number>
|
System-generated number used internally by Oracle. |
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 materialized view site. In addition to adding materialized views, 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 21-24 CREATE_TEMPLATE_OBJECT Function Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template to which you want to add this object. |
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 (that is, MATERIALIZED VIEW , TRIGGER , PROCEDURE , and so on). Objects of the following types can be specified:
MATERIALIZED VIEW PROCEDURE INDEX FUNCTION TABLE PACKAGE VIEW PACKAGE BODY SYNONYM TRIGGER SEQUENCE DATABASE LINK |
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. You can use a colon (:) to create a template parameter for your template object. See Chapter 4, "Create a Deployment Template" for more information.
When you add a materialized view with a |
master_rollback_seg |
Specifies the name of the rollback segment to use when executing the defined object DDL at the remote materialized view site. |
flavor_id |
This parameter is for internal use only.
Note: Do not set this parameter unless directed to do so by Oracle Support Services. |
Exceptions
Table 21-25 CREATE_TEMPLATE_OBJECT Function Exceptions
Exception | Description |
---|---|
miss_refresh_template |
Specified refresh template name is invalid or missing. Query the DBA_REPCAT_REFRESH_TEMPLATES view for a list of existing deployment templates. |
bad_object_type |
Object type is specified incorrectly. See Table 21-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 21-26 CREATE_TEMPLATE_OBJECT Function Returns
Return Value | Description |
---|---|
<system-generated number> | System-generated number used internally by Oracle. |
Usage Notes
Because CREATE_TEMPLATE_OBJECT
utilizes a CLOB
, you must use 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 MATERIALIZED VIEW mview_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 => 'mview_sales', object_type => 'MATERIALIZED VIEW', ddl_text => templob, master_rollback_seg => 'RBS'); DBMS_LOB.FREETEMPORARY(templob); END; /
This function creates parameters for a specific deployment template to allow custom data sets to be created at the remote materialized view 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 or prompt strings or both (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 21-27 CREATE_TEMPLATE_PARM Function Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template for which you want to create the parameter. |
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 whether 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 21-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 21-29 CREATE_TEMPLATE_PARM Function Returns
Return Value | Description |
---|---|
<system-generated number> | System-generated number used internally by Oracle. |
Usage Notes
Because the CREATE_TEMPLATE_PARM
function utilizes a CLOB
, you must use 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; /
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_USER_AUTHORIZATIONS
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 21-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 (for example, ' john, mike, bob ' ) |
refresh_template_name |
Name of the template that you want to authorize the specified user to instantiate. |
Exceptions
Table 21-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 ALL_REPCAT_USER_AUTHORIZATIONS view for a listing of template authorizations. |
Returns
Table 21-32 CREATE_USER_AUTHORIZATION Function Returns
Return Value | Description |
---|---|
<system-generated number> | System-generated number used internally by Oracle. |
This function predefines deployment template parameter values for specific users. For example, if you want to predefine 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 21-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 parameter 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 predefining a user parameter value for. |
parm_value |
The predefined parameter value that will be used during the instantiation process initiated by the specified user. |
Exceptions
Table 21-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_PARM_VALUES 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 21-35 CREATE_USER_PARM_VALUE Function Returns
Return Value | Description |
---|---|
<system-generated number> | System-generated number used internally by Oracle. |
Usage Notes
Because the CREATE_USER_PARM_VALUE
function utilizes a CLOB
, you must use 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; /
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 21-36 DELETE_RUNTIME_PARMS Procedure Parameters
Parameter | Description |
---|---|
runtime_parm_id |
Specifies the identification 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 view for a list of deployment template parameters). |
Exceptions
Table 21-37 DELETE_RUNTIME_PARMS Procedure Exceptions
Exception | Description |
---|---|
miss_template_parm |
The specified deployment template parameter name is invalid or missing. |
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 21-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 types can be specified:
MATERIALIZED VIEW PROCEDURE INDEX FUNCTION TABLE PACKAGE VIEW PACKAGE BODY SYNONYM TRIGGER SEQUENCE DATABASE LINK |
Exceptions
Table 21-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 21-38 for a list of valid object types. |
This procedure lets you drop template parameters for a specified deployment template. You can use this procedure to drop all parameters that are not referenced by a template object or to drop from the template all objects that reference any parameter, along with all of 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 21-40 DROP_ALL_TEMPLATE_PARMS Procedure Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template that contains the parameters and objects that you want to drop. |
drop_objects |
If no value is specified, then this parameter defaults to N, which drops all parameters not referenced by a template object.
If Y is specified, then all objects that reference any template parameter and the template parameters themselves are dropped. The objects are dropped from the template, not from the database. |
Exceptions
Table 21-41 DROP_ALL_TEMPLATE_PARMS Procedure Exceptions
Exception | Description |
---|---|
miss_refresh_template |
Specified deployment template name is invalid or does not exist. |
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.Syntax
DBMS_REPCAT_RGT.DROP_ALL_TEMPLATE_SITES ( refresh_template_name IN VARCHAR2);
Parameter
Table 21-42 DROP_ALL_TEMPLATE_SITES Procedure Parameter
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template that contains the sites that you want to drop. |
Exceptions
Table 21-43 DROP_ALL_TEMPLATE_SITES Procedure Exceptions
Exception | Description |
---|---|
miss_refresh_template |
Specified deployment template name is invalid or does not exist. |
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
This procedure enables the DBA to drop all user authorizations for a specified deployment template. Executing this procedure removes rows from the DBA_REPCAT_USER_AUTHORIZATIONS
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 21-44 DROP_ALL_USER_AUTHORIZATIONS Procedure Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template that contains the user authorizations that you want to drop. |
Exceptions
Table 21-45 DROP_ALL_USER_AUTHORIZATIONS Procedure Exceptions
Exception | Description |
---|---|
miss_refresh_template |
Specified deployment template name is invalid or does not exist. |
This procedure drops user parameter values for a specific deployment template. This procedure is very flexible and enables you to define a set of user parameter values to be deleted.
For example, defining the parameters shown in the following table has the described results.
Parameter | Result of Defining the Parameter |
---|---|
refresh_template_name |
Drops all user parameters for the specified deployment template |
refresh_template_name and user_name |
Drops all of the specified user parameters for the specified deployment template |
refresh_template_name and parameter_name |
Drops all user parameter values for the specified deployment template parameter |
refresh_template_name , parameter_name , and 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 21-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 21-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_VALUES view. |
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 21-48 DROP_REFRESH_TEMPLATE Procedure Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template to be dropped. |
Exceptions
Table 21-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_TEMPLATES view for a list of deployment templates. |
This procedure drops a template instantiation at any target site. This procedure removes all related metadata at the master site and disables the specified site from refreshing its materialized views.
Syntax
DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION ( refresh_template_name IN VARCHAR2, user_name IN VARCHAR2, site_name IN VARCHAR2);
Parameters
Table 21-50 DROP_SITE_INSTANTIATION Procedure Parameters
Parameter | Description |
---|---|
refresh_template_name |
The name of the template that contains the site to be dropped. |
user_name |
The name of the user who originally instantiated the template at the remote materialized view site. Query the ALL_REPCAT_TEMPLATE_SITES view to see the users that instantiated templates. See the ALL_REPCAT_TEMPLATE_SITES view for more information. |
site_name |
Identifies the template site to be dropped. |
Exceptions
Table 21-51 DROP_SITE_INSTANTIATION Procedure Exceptions
Exception | Description |
---|---|
miss_refresh_template |
The deployment template name specified is invalid or does not exist. |
miss_user |
The username specified does not exist. |
miss_template_site |
The deployment template has not been instantiated for user and site. |
This procedure removes a template object from a specific deployment template. For example, a DBA would use this procedure to remove an outdated materialized view 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 must reinstantiate 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 21-52 DROP_TEMPLATE_OBJECT Procedure Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template from which you are dropping the object. |
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 types can be specified:
MATERIALIZED VIEW PROCEDURE INDEX FUNCTION TABLE PACKAGE VIEW PACKAGE BODY SYNONYM TRIGGER SEQUENCE DATABASE LINK |
Exceptions
Table 21-53 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_OBJECTS view to see a list of deployment template objects. |
This procedure removes an existing template parameter from the DBA_REPCAT_TEMPLATE_PARMS
view. This procedure is useful 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 21-54 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 21-55 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. |
This procedure removes a user authorization entry from the DBA_REPCAT_USER_AUTHORIZATIONS
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 21-56 DROP_USER_AUTHORIZATION Procedure Parameters
Parameter | Description |
---|---|
refresh_template_name |
Name of the deployment template from which the user's authorization is being removed. |
user_name |
Name of the user whose authorization is being removed. |
Exceptions
Table 21-57 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_USER_AUTHORIZATIONS view to see a list of user/deployment template authorizations. |
miss_refresh_template |
Specified deployment template name is invalid or does not exist. |
This procedure removes a predefined 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 21-58 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 predefined value that you want to drop. |
user_name |
Name of the user whose parameter value you want to drop. |
Exceptions
Table 21-59 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_VALUES view. |
This function retrieves an identification to be used when defining a runtime parameter value. All runtime parameter values are assigned to this identification and are also used during the instantiation process.
Syntax
DBMS_REPCAT_RGT.GET_RUNTIME_PARM_ID RETURN NUMBER;
Parameters
None
Returns
Table 21-60 GET_RUNTIME_PARM_ID Function Returns
Return Value | Corresponding Datatype |
---|---|
<system-generated number> | Runtime parameter values are assigned to the system-generated number and are also used during the instantiation process. |
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 identification to use when inserting a runtime parameter. This identification is used for defining runtime parameter values and instantiating deployment templates.
Syntax
DBMS_REPCAT_RGT.INSERT_RUNTIME_PARMS ( runtime_parm_id IN NUMBER, parameter_name IN VARCHAR2, parameter_value IN CLOB);
Parameters
Table 21-61 INSERT_RUNTIME_PARMS Procedure Parameters
Parameter | Description |
---|---|
runtime_parm_id |
The identification retrieved by the GET_RUNTIME_PARM_ID function. This identification is also used when instantiating the deployment template. Be sure to use the same identification for all parameter values for a deployment template. |
parameter_name |
Name of the template parameter for which you are defining a runtime parameter value. 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 21-62 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_VALUES view. |
Usage Notes
Because the this procedure utilizes a CLOB
, you must use 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; /
This function generates a script at the master site that is used to create the materialized view environment at the remote materialized view site while the materialized view site disconnected from the master (that is, while the materialized view site is offline). This generated script should be used at remote materialized view sites that are not able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote materialized view site might be lengthy (depending on the amount of data that is populated to the new materialized views). This function must 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
temporary view.
Note:
This function is used to perform an offline instantiation of a deployment template. Additionally, this function is for replication administrators who are instantiating for another user. Users wanting to perform their own instantiation should use the public version of theINSTANTIATE_OFFLINE
function. See the "INSTANTIATE_OFFLINE Function" for more information.
This function should not be confused with the procedures in the DBMS_OFFLINE_OG package (used for performing an offline instantiation of a master table). See the documentation for this package 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', use_default_gowner IN BOOLEAN := true) RETURN NUMBER;
Parameters
Table 21-63 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 who is instantiating the deployment template. |
runtime_parm_id |
If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, then specify the identification used when creating the runtime parameters (the identification 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. |
use_default_gowner |
If true , then any materialized view groups created are owned by the default user PUBLIC . If false , then any materialized view groups created are owned by the user performing the instantiation. |
Exceptions
Table 21-64 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_USER_AUTHORIZATIONS view. If user is not listed, then the specified user is not authorized to instantiate the target deployment template. |
Returns
Table 21-65 INSTANTIATE_OFFLINE Function Returns
Return Value | Description |
---|---|
<system-generated number> | Specifies the generated system number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT temporary view to retrieve the generated instantiation script. |
This function generates a script at the master site that is used to create the materialized view environment at the remote materialized view site while the materialized view site is connected to the master (that is, while the materialized view site is online). This generated script should be used at remote materialized view sites that are able to remain connected to the master site for an extended amount of time, as the instantiation process at the remote materialized view site might be lengthy (depending on the amount of data that is populated to the new materialized views). This function must 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
temporary view.
Note:
This function is for replication administrators who are instantiating for another user. Users wanting to perform their own instantiation should use the public version of theINSTANTIATE_OFFLINE
function, described in "INSTANTIATE_OFFLINE Function" section.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', use_default_gowner IN BOOLEAN := true) RETURN NUMBER;
Parameters
Table 21-66 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 who is instantiating the deployment template. |
runtime_parm_id |
If you have defined runtime parameter values using the INSERT_RUNTIME_PARMS procedure, then specify the identification used when creating the runtime parameters (the identification 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. |
use_default_gowner |
If true , then any materialized view groups created are owned by the default user PUBLIC . If false , then any materialized view groups created are owned by the user performing the instantiation. |
Exceptions
Table 21-67 INSTANTIATE_ONLINE Function Exceptions
Exception | Description |
---|---|
miss_refresh_template |
Specified deployment template name 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_USER_AUTHORIZATIONS view. If user is not listed, then the specified user is not authorized to instantiate the target deployment template. |
bad_parms |
Not all of the template parameters were populated by the defined user parameter values or template default values or both. The number of predefined values might not have matched the number of template parameters or a predefined value was invalid for the target parameter (that is, type mismatch). |
Returns
Table 21-68 INSTANTIATE_ONLINE Function Returns
Return Value | Description |
---|---|
<system-generated number> | Specifies the system-generated number for the output_id when you select from the USER_REPCAT_TEMP_OUTPUT temporary view to retrieve the generated instantiation script. |
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 is 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
The LOCK_TEMPLATE_SHARED
procedure is used to make a specified deployment template "read-only." This procedure should be called before instantiating a template, as this ensures that nobody can change the deployment template while it is being instantiated.
The lock is released when a ROLLBACK
or COMMIT
is performed.
Syntax
DBMS_REPCAT_RGT.LOCK_TEMPLATE_SHARED();
Parameters
None