15
DBMS_APPLY_ADM

The DBMS_APPLY_ADM package, one of a set of Streams packages, provides administrative interfaces to start, stop, and configure an apply process. This package includes subprograms for configuring apply handlers, setting enqueue destinations for events, and specifying execution directives for events. This package also provides administrative subprograms that set the instantiation SCN for objects at a destination database. This package also includes subprograms for managing apply errors.

See Also:

Oracle Streams Concepts and Administration and Oracle Streams Replication Administrator's Guide for more information about this package and apply processes

This chapter contains the following topic:

Summary of DBMS_APPLY_ADM Subprograms

Summary of DBMS_APPLY_ADM Subprograms

Table 15-1 DBMS_APPLY_ADM Package Subprograms

Subprogram	Description
ALTER_APPLY Procedure	Alters an apply process
COMPARE_OLD_VALUES Procedure	Specifies whether to compare old value of one or more columns in a row LCR with the current value of the corresponding columns at the destination site during apply
CREATE_APPLY Procedure	Creates an apply process
DELETE_ALL_ERRORS Procedure	Deletes all the error transactions for the specified apply process
DELETE_ERROR Procedure	Deletes the specified error transaction
DROP_APPLY Procedure	Drops an apply process
EXECUTE_ALL_ERRORS Procedure	Reexecutes the error transactions for the specified apply process.
EXECUTE_ERROR Procedure	Reexecutes the specified error transaction
GET_ERROR_MESSAGE Functions	Returns the message payload from the error queue for the specified message number and transaction identifier
SET_DML_HANDLER Procedure	Alters operation options for a specified object with a specified apply process
SET_ENQUEUE_DESTINATION Procedure	Sets the queue where an event that satisfies the specified rule is enqueued automatically by an apply process
SET_EXECUTE Procedure	Specifies whether an event that satisfies the specified rule is executed by an apply process
SET_GLOBAL_INSTANTIATION_SCN Procedure	Records the specified instantiation SCN for the specified source database and, optionally, for the schemas at the source database and the tables owned by these schemas
SET_KEY_COLUMNS Procedures	Records the set of columns to be used as the substitute primary key for local apply purposes and removes existing substitute primary key columns for the specified object if they exist
SET_PARAMETER Procedure	Sets an apply parameter to the specified value
SET_SCHEMA_INSTANTIATION_SCN Procedure	Records the specified instantiation SCN for the specified schema in the specified source database and, optionally, for the tables owned by the schema at the source database
SET_TABLE_INSTANTIATION_SCN Procedure	Records the specified instantiation SCN for the specified table in the specified source database
SET_UPDATE_CONFLICT_HANDLER Procedure	Adds, updates, or drops an update conflict handler for the specified object
START_APPLY Procedure	Directs the apply process to start applying events
STOP_APPLY Procedure	Stops the apply process from applying any events and rolls back any unfinished transactions being applied

Note:

All procedures and functions commit unless specified otherwise.

ALTER_APPLY Procedure

This procedure alters an apply process.

Syntax

  DBMS_APPLY_ADM.ALTER_APPLY(
     apply_name                IN  VARCHAR2,
     rule_set_name             IN  VARCHAR2  DEFAULT NULL,
     remove_rule_set           IN  BOOLEAN   DEFAULT false,
     message_handler           IN  VARCHAR2  DEFAULT NULL
     remove_message_handler    IN  BOOLEAN   DEFAULT false,
     ddl_handler               IN  VARCHAR2  DEFAULT NULL,
     remove_ddl_handler        IN  BOOLEAN   DEFAULT false,
     apply_user                IN  VARCHAR2  DEFAULT NULL,
     apply_tag                 IN  RAW       DEFAULT NULL,
     remove_apply_tag          IN  BOOLEAN   DEFAULT false,
     precommit_handler         IN  VARCHAR2  DEFAULT NULL,
     remove_precommit_handler  IN  BOOLEAN   DEFAULT false,
     negative_rule_set_name    IN  VARCHAR2  DEFAULT NULL,
     remove_negative_rule_set  IN  BOOLEAN   DEFAULT false);

Parameters

Table 15-2 ALTER_APPLY Procedure Parameters

Parameter	Description
`apply_name`	The name of the apply process being altered. You must specify an existing apply process name. Do not specify an owner.
`rule_set_name`	The name of the positive rule set for the apply process. The positive rule set contains the rules that instruct the apply process to apply events. If you want to use a positive rule set for the apply process, then you must specify an existing rule set in the form `[``schema_name``.]``rule_set_name`. For example, to specify a positive rule set in the `hr` schema named `job_apply_rules`, enter `hr.job_apply_rules`. If the schema is not specified, then the current user is the default. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify `NULL` and the `remove_rule_set` parameter is set to `false`, then retains any existing positive rule set. If you specify `NULL` and the `remove_rule_set` parameter is set to `true`, then removes any existing positive rule set.
`remove_rule_set`	If `true`, then removes the positive rule set for the specified apply process. If you remove the positive rule set for an apply process, and the apply process does not have a negative rule set, then the apply process dequeues all events in its queue. If you remove the positive rule set for an apply process, and a negative rule set exists for the apply process, then the apply process dequeues all events in its queue that are not discarded by the negative rule set. If `false`, then retains the positive rule set for the specified apply process. If the `rule_set_name` parameter is non-`NULL`, then this parameter should be set to `false`.
`message_handler`	A user-defined procedure that processes non-LCR messages in the queue for the apply process. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` If the procedure is in a package, then the `package_name` must be specified. For example, to specify a procedure in the `apply_pkg` package in the `hr` schema named `process_msgs`, enter `hr.apply_pkg.process_msgs`. An error is returned if the specified procedure does not exist. The user who invokes the `ALTER_APPLY` procedure must have `EXECUTE` privilege on a specified message handler. Also, if the `schema_name` is not specified, then the user who invokes the `ALTER_APPLY` procedure is the default.
`remove_message_handler`	If `true`, then removes the message handler for the specified apply process. If `false`, then retains any message handler for the specified apply process. If the `message_handler` parameter is non-`NULL`, then this parameter should be set to `false`.
`ddl_handler`	A user-defined procedure that processes DDL LCRs in the queue for the apply process. You must specify an existing procedure in the form `[``schema_name``.]``procedure_name`. For example, to specify a procedure in the `hr` schema named `process_ddls`, enter `hr.process_ddls`. An error is returned if the specified procedure does not exist. The user who invokes the `ALTER_APPLY` procedure must have `EXECUTE` privilege on a specified DDL handler. Also, if the `schema_name` is not specified, then the user who invokes the `ALTER_APPLY` procedure is the default. All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the `EXECUTE` member procedure of a DDL LCR, then a commit is performed automatically.
`remove_ddl_handler`	If `true`, then removes the DDL handler for the specified apply process. If `false`, then retains any DDL handler for the specified apply process. If the `ddl_handler` parameter is non-`NULL`, then this parameter should be set to `false`.
`apply_user`	The user who applies all DML and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. If `NULL`, then the apply user is not changed. To change the apply user, the user who invokes the `ALTER_APPLY` procedure must be granted `DBA` role. Only the `SYS` user can set the `apply_user` to `SYS`. If you change the apply user, then this procedure grants the new apply user dequeue privilege on the queue used by the apply process and configures the user as a secure queue user of the queue. In addition, make sure the apply user has the following privileges: Execute privilege on the rule sets used by the apply process Execute privilege on all rule-based transformation functions used in the rule set Execute privilege on all apply handler procedures Execute privilege on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the apply process These privileges must be granted directly to the apply user. They cannot be granted through roles. By default, this parameter is set to the user who created the apply process by running either the `CREATE_APPLY` procedure in this package or one of the following procedures in the `DBMS_STREAMS_ADM` package with the `streams_type` parameter set to `apply`: `ADD_GLOBAL_RULES`, `ADD_SCHEMA_RULES`, `ADD_TABLE_RULES`, and `ADD_SUBSET_RULES`. Note: If the specified user is dropped using `DROP` `USER...` `CASCADE`, then the `apply_user` for the apply process is set to `NULL` automatically. You must specify an apply user before the apply process can run.
`apply_tag`	A binary tag that is added to redo entries generated by the specified apply process. The tag is a binary value that can be used to track LCRs. The tag is relevant only if a capture process at the database where the apply process is running will capture changes made by the apply process. If so, then the captured changes will include the tag specified by this parameter. If `NULL`, the default, then the apply tag for the apply process is not changed. The following is an example of a tag with a hexadecimal value of `17`: HEXTORAW('17') See Also: Oracle Streams Replication Administrator's Guide for more information about tags
`remove_apply_tag`	If `true`, then sets the apply tag for the specified apply process to `NULL`, and the apply process generates redo entries with `NULL` tags. If `false`, then retains any apply tag for the specified apply process. If the `apply_tag` parameter is non-`NULL`, then this parameter should be set to `false`.
`precommit_handler`	A user-defined procedure that can receive internal commit directives in the queue for the apply process before they are processed by the apply process. Typically, precommit handlers are used for auditing commit information for transactions processed by an apply process. An internal commit directive is enqueued into a queue when a capture process captures the commit directive for a transaction that contains row LCRs that were captured, and when a user or application enqueues messages into a queue and then issues a `COMMIT` statement. For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database, but for a user-enqueued event, the commit SCN is generated by the apply process. You must specify an existing procedure in the form `[``schema_name``.]``procedure_name`. For example, to specify a procedure in the `hr` schema named `process_commits`, enter `hr.process_commits`. An error is returned if the specified procedure does not exist. The user who invokes the `ALTER_APPLY` procedure must have `EXECUTE` privilege on a specified precommit handler. Also, if the `schema_name` is not specified, then the user who invokes the `ALTER_APPLY` procedure is the default. The precommit handler procedure must conform to the following restrictions: Any work that commits must be an autonomous transaction. Any rollback must be to a named savepoint created in the procedure. If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the events in the transaction are moved to the error queue.
`remove_precommit_handler`	If `true`, then removes the precommit handler for the specified apply process. If `false`, then retains any precommit handler for the specified apply process. If the `precommit_handler` parameter is non-`NULL`, then this parameter should be set to `false`.
`negative_rule_set_name`	The name of the negative rule set for the apply process. The negative rule set contains the rules that instruct the apply process to discard events. If you want to use a negative rule set for the apply process, then you must specify an existing rule set in the form `[``schema_name``.]``rule_set_name`. For example, to specify a negative rule set in the `hr` schema named `neg_apply_rules`, enter `hr.neg_apply_rules`. If the schema is not specified, then the current user is the default. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify `NULL` and the `remove_negative_rule_set` parameter is set to `false`, then retains any existing negative rule set. If you specify `NULL` and the `remove_negative_rule_set` parameter is set to `true`, then removes any existing negative rule set. If you specify both a positive and a negative rule set for an apply process, then the negative rule set is always evaluated first.
`remove_negative_rule_set`	If `true`, then removes the negative rule set for the specified apply process. If you remove the negative rule set for an apply process, and the apply process does not have a positive rule set, then the apply process dequeues all events in its queue. If you remove the negative rule set for an apply process, and a positive rule set exists for the apply process, then the apply process dequeues all events in its queue that are not discarded by the positive rule set. If `false`, then retains the negative rule set for the specified apply process. If the `negative_rule_set_name` parameter is non-`NULL`, then this parameter should be set to `false`.

Usage Notes

An apply process is stopped and restarted automatically when you change the value of one or more of the following ALTER_APPLY procedure parameters:

message_handler
ddl_handler
apply_user
apply_tag
precommit_handler

COMPARE_OLD_VALUES Procedure

This procedure specifies whether to compare the old value of one or more columns in a row LCR with the current value of the corresponding columns at the destination site during apply. This procedure is relevant only for UPDATE and DELETE operations because only these operations result in old column values in row LCRs. The default is to compare old values for all columns.

See Also:

Oracle Streams Replication Administrator's Guide for more information about conflict detection and resolution in a Streams environment

Syntax

DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
   object_name         IN VARCHAR2,
   { column_list       IN VARCHAR2, 
  | column_table      IN DBMS_UTILITY.LNAME_ARRAY,}
   operation           IN VARCHAR2 DEFAULT 'UPDATE',
   compare             IN BOOLEAN  DEFAULT true,
   apply_database_link IN VARCHAR2 DEFAULT NULL);

Note:

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.

Parameters

Table 15-3 COMPARE_OLD_VALUES Procedure Parameters

Parameter	Description
`object_name`	The name of the source table specified as `[``schema_name``.]``object_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default.
`column_list`	A comma-delimited list of the columns in the table. There must be no spaces between entries. Specify `*` to include all nonkey columns.
`column_table`	Instead of a list, you can use a PL/SQL index-by table of type `DBMS_UTILITY.LNAME_ARRAY` to contain the column names. The first column name should be at position 1, the second at position 2, and so on. The table does not need to be `NULL` terminated.
`operation`	The name of the operation, which can be specified as: `UPDATE` for `UPDATE` operations `DELETE` for `DELETE` operations `*` for both `UPDATE` and `DELETE` operations
`compare`	If compare is `true`, the old values of the specified columns are compared during apply. If compare is `false`, the old values of the specified columns are not compared during apply.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.

Usage Notes

By default, an apply process uses the old column values in a row LCR to detect conflicts. You may choose not to compare old column values to avoid conflict detection for specific tables. For example, if you use a time column for conflict detection, then an apply process does not need to check old values for nonkey and non time columns.

Note:

An apply process always compares old values for key columns when they are present in a row LCR. This procedure raises an error if a key column is specified and the compare parameter is set to false.

CREATE_APPLY Procedure

This procedure creates an apply process.

Syntax

  DBMS_APPLY_ADM.CREATE_APPLY(
     queue_name              IN  VARCHAR2,
     apply_name              IN  VARCHAR2,
     rule_set_name           IN  VARCHAR2  DEFAULT NULL,
     message_handler         IN  VARCHAR2  DEFAULT NULL,
     ddl_handler             IN  VARCHAR2  DEFAULT NULL,
     apply_user              IN  VARCHAR2  DEFAULT NULL,
     apply_database_link     IN  VARCHAR2  DEFAULT NULL,
     apply_tag               IN  RAW       DEFAULT '00',
     apply_captured          IN  BOOLEAN   DEFAULT false,
     precommit_handler       IN  VARCHAR2  DEFAULT NULL,
     negative_rule_set_name  IN  VARCHAR2  DEFAULT NULL,
     source_database         IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 15-4 CREATE_APPLY Procedure Parameters

Parameter	Description
`queue_name`	The name of the queue from which the apply process dequeues LCRs and user messages. You must specify an existing queue in the form `[``schema_name``.]``queue_name`. For example, to specify a queue in the `hr` schema named `streams_queue`, enter `hr.streams_queue`. If the schema is not specified, then the current user is the default. Note: The `queue_name` setting cannot be altered after the apply process is created.
`apply_name`	The name of the apply process being created. A `NULL` specification is not allowed. Do not specify an owner. The specified name must not match the name of an existing apply process or messaging client. Note: The `apply_name` setting cannot be altered after the apply process is created.
`rule_set_name`	The name of the positive rule set for the apply process. The positive rule set contains the rules that instruct the apply process to apply events. If you want to use a positive rule set for the apply process, then you must specify an existing rule set in the form `[``schema_name``.]``rule_set_name`. For example, to specify a positive rule set in the `hr` schema named `job_apply_rules`, enter `hr.job_apply_rules`. If the schema is not specified, then the current user is the default. If you specify `NULL`, and no negative rule set is specified, then the apply process applies either all captured events or all user-enqueued events in the queue, depending on the setting of the `apply_captured` parameter. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package.
`message_handler`	A user-defined procedure that processes non-LCR messages in the queue for the apply process. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` If the procedure is in a package, then the `package_name` must be specified. For example, to specify a procedure in the `apply_pkg` package in the `hr` schema named `process_msgs`, enter `hr.apply_pkg.process_msgs`. An error is returned if the specified procedure does not exist. The user who invokes the `CREATE_APPLY` procedure must have `EXECUTE` privilege on a specified message handler. Also, if the `schema_name` is not specified, then the user who invokes the `CREATE_APPLY` procedure is the default. See "Usage Notes" for more information about a message handler procedure.
`ddl_handler`	A user-defined procedure that processes DDL LCRs in the queue for the apply process. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` If the procedure is in a package, then the `package_name` must be specified. For example, to specify a procedure in the `apply_pkg` package in the `hr` schema named `process_ddls`, enter `hr.apply_pkg.process_ddls`. An error is returned if the specified procedure does not exist. The user who invokes the `CREATE_APPLY` procedure must have `EXECUTE` privilege on a specified DDL handler. Also, if the `schema_name` is not specified, then the user who invokes the `CREATE_APPLY` procedure is the default. All applied DDL LCRs commit automatically. Therefore, if a DDL handler calls the `EXECUTE` member procedure of a DDL LCR, then a commit is performed automatically. See "Usage Notes" for more information about a DDL handler procedure.
`apply_user`	The user who applies all DML and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. If `NULL`, then the user who runs the `CREATE_APPLY` procedure is used. Only a user who is granted `DBA` role can set an apply user. Only the `SYS` user can set the `apply_user` to `SYS`. Note: If the specified user is dropped using `DROP` `USER...` `CASCADE`, then the `apply_user` setting for the apply process is set to `NULL` automatically. You must specify an apply user before the apply process can run.
`apply_tag`	A binary tag that is added to redo entries generated by the specified apply process. The tag is a binary value that can be used to track LCRs. The tag is relevant only if a capture process at the database where the apply process is running will capture changes made by the apply process. If so, then the captured changes will include the tag specified by this parameter. By default, the tag for an apply process is the hexadecimal equivalent of `'00'` (double zero). The following is an example of a tag with a hexadecimal value of `17`: HEXTORAW('17') If `NULL`, then the apply process generates redo entries with `NULL` tags. See Also: Oracle Streams Replication Administrator's Guide for more information about tags
`apply_captured`	Either `true` or `false`. If `true`, then the apply process applies only the events in a queue that were captured by a Streams capture process. If `false`, then the apply process applies only the user-enqueued events in a queue. These events are user messages that were not captured by a Streams capture process. These messages may or may not contain a user-created LCR. To apply both captured and user-enqueued events in a queue, you must create at least two apply processes. Note: The `apply_captured` setting cannot be altered after the apply process is created. See Also: Oracle Streams Concepts and Administration for more information about processing captured or user-enqueued events with an apply process
`precommit_handler`	A user-defined procedure that can receive internal commit directives in the queue for the apply process before they are processed by the apply process. Typically, precommit handlers are used for auditing commit information for transactions processed by an apply process. An internal commit directive is enqueued into a queue when a capture process captures the commit directive for a transaction that contains row LCRs that were captured, and when a user or application enqueues messages into a queue and then issues a `COMMIT` statement. For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database, but for a user-enqueued event, the commit SCN is generated by the apply process. You must specify an existing procedure in one of the following forms: `[``schema_name``.]``procedure_name` `[``schema_name``.]``package_name.procedure_name` If the procedure is in a package, then the `package_name` must be specified. For example, to specify a procedure in the `apply_pkg` package in the `hr` schema named `process_commits`, enter `hr.apply_pkg.process_commits`. An error is returned if the specified procedure does not exist. The user who invokes the `CREATE_APPLY` procedure must have `EXECUTE` privilege on a specified precommit handler. Also, if the `schema_name` is not specified, then the user who invokes the `CREATE_APPLY` procedure is the default. If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the events in the transaction are moved to the error queue. The precommit handler procedure must conform to the following restrictions: Any work that commits must be an autonomous transaction. Any rollback must be to a named savepoint created in the procedure. See "Usage Notes" for more information about a precommit handler procedure.
`negative_rule_set_name`	The name of the negative rule set for the apply process. The negative rule set contains the rules that instruct the apply process to discard events. If you want to use a negative rule set for the apply process, then you must specify an existing rule set in the form `[``schema_name``.]``rule_set_name`. For example, to specify a negative rule set in the `hr` schema named `neg_apply_rules`, enter `hr.neg_apply_rules`. If the schema is not specified, then the current user is the default. If you specify `NULL`, and no positive rule set is specified, then the apply process applies either all captured events or all user-enqueued events in the queue, depending on the setting of the `apply_captured` parameter. An error is returned if the specified rule set does not exist. You can create a rule set and add rules to it using the `DBMS_STREAMS_ADM` package or the `DBMS_RULE_ADM` package. If you specify both a positive and a negative rule set for an apply process, then the negative rule set is always evaluated first.
`source_database`	The global name of the source database of the changes that will be applied by the apply process. The source database is the database where the changes originated. If an apply process applies captured events, then the apply process can apply events from only one capture process at one source database. If `NULL`, then the source database name of the first LCR received by the apply process is used for the source database. If you do not include the domain name, then it is appended to the database name automatically. For example, if you specify `DBS1` and the domain is `.NET`, then `DBS1.NET` is specified automatically. The rules in the apply process rule sets determine which events are dequeued by the apply process. If the apply process dequeues an LCR with a source database that is different than the source database for the apply process, then an error is raised. You can determine the source database for an apply process by querying the `DBA_APPLY_PROGRESS` data dictionary view.

Usage Notes

The user who invokes this procedure must be granted DBA role.

The apply_user parameter specifies the user who applies changes that satisfy the apply process rule sets and who runs user-defined apply handlers. This user must have the necessary privileges to apply changes. This procedure grants the apply user dequeue privilege on the queue used by the apply process and configures the user as a secure queue user of the queue.

In addition, make sure the apply user has the following privileges:

The necessary privileges to perform DML and DDL changes on the apply objects
Execute privilege on the rule sets used by the apply process
Execute privilege on all rule-based transformation functions used in the rule set
Execute privilege on all apply handler procedures
Execute privilege on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the apply process

These privileges must be granted directly to the apply user. They cannot be granted through roles.

The following sections describe the PL/SQL procedures that are specified as message handlers, DDL handlers, and precommit handlers.

Message Handler and DDL Handler Procedure

The procedure specified in both the message_handler parameter and the ddl_handler parameter must have the following signature:

PROCEDURE handler_procedure (
parameter_name   IN  SYS.AnyData);

Here, handler_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. For the message handler, the parameter passed to the procedure is a SYS.AnyData encapsulation of a user message. For the DDL handler procedure, the parameter passed to the procedure is a SYS.AnyData encapsulation of a DDL LCR.

See Also:

Chapter 174, "Logical Change Record TYPEs" for information about DDL LCRs

Precommit Handler Procedure

The procedure specified in the precommit_handler parameter must have the following signature:

PROCEDURE handler_procedure (
parameter_name   IN  NUMBER);

Here, handler_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is the commit SCN of a commit directive.

DELETE_ALL_ERRORS Procedure

This procedure deletes all the error transactions for the specified apply process.

Syntax

DBMS_APPLY_ADM.DELETE_ALL_ERRORS(
   apply_name  IN  VARCHAR2 DEFAULT NULL);

Parameter

Table 15-5 DELETE_ALL_ERRORS Procedure Parameter

Parameter Description

Parameter	Description
`apply_name`	The name of the apply process that raised the errors while processing the transactions. Do not specify an owner. If `NULL`, then all error transactions for all apply processes are deleted.

apply_name

The name of the apply process that raised the errors while processing the transactions. Do not specify an owner.

If NULL, then all error transactions for all apply processes are deleted.

DELETE_ERROR Procedure

This procedure deletes the specified error transaction.

Syntax

DBMS_APPLY_ADM.DELETE_ERROR(
   local_transaction_id  IN  VARCHAR2);

Parameter

Table 15-6 DELETE_ERROR Procedure Parameter

Parameter	Description
`local_transaction_id`	The identification number of the error transaction to delete. If the specified transaction does not exist in the error queue, then an error is raised.

DROP_APPLY Procedure

This procedure drops an apply process.

Syntax

  DBMS_APPLY_ADM.DROP_APPLY(
     apply_name             IN  VARCHAR2,
     drop_unused_rule_sets  IN  BOOLEAN  DEFAULT false);

Parameters

Table 15-7 DROP_APPLY Procedure Parameters

Parameter Description

Parameter	Description
`apply_name`	The name of the apply process being dropped. You must specify an existing apply process name. Do not specify an owner.
`drop_unused_rule_sets`	If `true`, then drops any rule sets, positive and negative, used by the specified apply process if these rule sets are not used by any other Streams client, which includes capture processes, propagations, apply processes, and messaging clients. If this procedure drops a rule set, then this procedure also drops any rules in the rule set that are not in another rule set. If `false`, then does not drop the rule sets used by the specified apply process, and the rule sets retain their rules.

apply_name

The name of the apply process being dropped. You must specify an existing apply process name. Do not specify an owner.

drop_unused_rule_sets

If true, then drops any rule sets, positive and negative, used by the specified apply process if these rule sets are not used by any other Streams client, which includes capture processes, propagations, apply processes, and messaging clients. If this procedure drops a rule set, then this procedure also drops any rules in the rule set that are not in another rule set.

If false, then does not drop the rule sets used by the specified apply process, and the rule sets retain their rules.

Usage Notes

When you use this procedure to drop an apply process, information about rules created for the apply process using the DBMS_STREAMS_ADM package is removed from the data dictionary views for Streams rules. Information about such a rule is removed even if the rule is not in either rule set for the apply process. The following are the data dictionary views for Streams rules:

ALL_STREAMS_GLOBAL_RULES
DBA_STREAMS_GLOBAL_RULES
ALL_STREAMS_MESSAGE_RULES
DBA_STREAMS_MESSAGE_RULES
ALL_STREAMS_SCHEMA_RULES
DBA_STREAMS_SCHEMA_RULES
ALL_STREAMS_TABLE_RULES
DBA_STREAMS_TABLE_RULES

See Also:
Oracle Streams Concepts and Administration for more information about Streams data dictionary views

EXECUTE_ALL_ERRORS Procedure

This procedure reexecutes the error transactions for the specified apply process.

The transactions are reexecuted in commit SCN order. Error reexecution stops if an error is raised.

Syntax

DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(
   apply_name       IN  VARCHAR2  DEFAULT NULL,
   execute_as_user  IN  BOOLEAN   DEFAULT false);

Parameters

Table 15-8 EXECUTE_ALL_ERRORS Procedure Parameters

Parameter Description

Parameter	Description
`apply_name`	The name of the apply process that raised the errors while processing the transactions. Do not specify an owner. If `NULL`, then all error transactions for all apply processes are reexecuted.
`execute_as_user`	If `true`, then reexecutes the transactions in the security context of the current user. If `false`, then reexecutes each transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised. The `DBA_APPLY_ERROR` data dictionary view lists the original receiver for each error transaction. The user who executes the transactions must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.

apply_name

The name of the apply process that raised the errors while processing the transactions. Do not specify an owner.

If NULL, then all error transactions for all apply processes are reexecuted.

execute_as_user

If true, then reexecutes the transactions in the security context of the current user.

If false, then reexecutes each transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised. The DBA_APPLY_ERROR data dictionary view lists the original receiver for each error transaction.

The user who executes the transactions must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.

EXECUTE_ERROR Procedure

This procedure reexecutes the specified error transaction.

Syntax

DBMS_APPLY_ADM.EXECUTE_ERROR(
   local_transaction_id  IN  VARCHAR2,
   execute_as_user       IN  BOOLEAN   DEFAULT false);

Parameters

Table 15-9 EXECUTE_ERROR Procedure Parameters

Parameter Description

Parameter	Description
`local_transaction_id`	The identification number of the error transaction to execute. If the specified transaction does not exist in the error queue, then an error is raised.
`execute_as_user`	If `true`, then reexecutes the transaction in the security context of the current user. If `false`, then reexecutes the transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised. The `DBA_APPLY_ERROR` data dictionary view lists the original receiver for each error transaction. The user who executes the transaction must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.

local_transaction_id

The identification number of the error transaction to execute. If the specified transaction does not exist in the error queue, then an error is raised.

execute_as_user

If true, then reexecutes the transaction in the security context of the current user.

If false, then reexecutes the transaction in the security context of the original receiver of the transaction. The original receiver is the user who was processing the transaction when the error was raised. The DBA_APPLY_ERROR data dictionary view lists the original receiver for each error transaction.

The user who executes the transaction must have privileges to perform DML and DDL changes on the apply objects and to run any apply handlers. This user must also have dequeue privileges on the queue used by the apply process.

GET_ERROR_MESSAGE Functions

This function returns the message payload from the error queue for the specified message number and transaction identifier. The message is an event, which may be a logical change record (LCR) or a non-LCR event.

One version of this function contains two OUT parameters. These OUT parameters contain the destination queue into which the event should be enqueued, if one exists, and whether or not the event should be executed. The destination queue is specified using the SET_ENQUEUE_DESTINATION procedure, and the execution directive is specified using the SET_EXECUTE procedure.

See Also:

Syntax

DBMS_APPLY_ADM.GET_ERROR_MESSAGE(
   message_number          IN   NUMBER, 
   local_transaction_id    IN   VARCHAR2,
   destination_queue_name  OUT  VARCHAR2, 
   execute                 OUT  BOOLEAN)
RETURN SYS.AnyData;

Note:

This function is overloaded. One version of this function contains two OUT parameters, and the other does not.

Parameters

Table 15-10 GET_ERROR_MESSAGE Function Parameters

Parameter Description

Parameter	Description
`local_transaction_id`	Identifier of the error transaction for which to return a message
`destination_queue_name`	Contains the name of the queue into which the message should be enqueued. If the message should not be enqueued into a queue, then contains `NULL`.
`execute`	Contains `true` if the message should be executed Contains `false` if the message should not be executed

local_transaction_id

Identifier of the error transaction for which to return a message

destination_queue_name

Contains the name of the queue into which the message should be enqueued. If the message should not be enqueued into a queue, then contains NULL.

execute

Contains true if the message should be executed

Contains false if the message should not be executed

SET_DML_HANDLER Procedure

This procedure sets a user procedure as a DML handler for a specified operation on a specified object. The user procedure alters the apply behavior for the specified operation on the specified object.

Syntax

DBMS_APPLY_ADM.SET_DML_HANDLER(
   object_name          IN  VARCHAR2,
   object_type          IN  VARCHAR2,
   operation_name       IN  VARCHAR2,
   error_handler        IN  BOOLEAN  DEFAULT false,
   user_procedure       IN  VARCHAR2,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL,
   apply_name           IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 15-11 SET_DML_HANDLER Procedure Parameters

Parameter	Description
`object_name`	The name of the source object specified as `[``schema_name``.]``object_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default. The specified object does not need to exist in the local destination database when you run this procedure.
`object_type`	The type of the source object. Currently, `TABLE` is the only possible source object type.
`operation_name`	The name of the operation, which can be specified as: `INSERT` `UPDATE` `DELETE` `LOB_UPDATE` For example, suppose you run this procedure twice for the `hr.employees` table. In one call, you set `operation_name` to `UPDATE` and `user_procedure` to `employees_update`. In another call, you set `operation_name` to `INSERT` and `user_procedure` to `employees_insert`. Both times, you set `error_handler` to `false`. In this case, the `employees_update` procedure is run for `UPDATE` operations on the `hr.employees` table, and the `employees_insert` procedure is run for `INSERT` operations on the `hr.employees` table.
`error_handler`	If `true`, then the specified user procedure is run when a row LCR involving the specified operation on the specified object raises an apply process error. The user procedure may try to resolve possible error conditions, or it may simply notify administrators of the error or log the error. If `false`, then the handler being set is run for all row LCRs involving the specified operation on the specified object. Note: Currently, error handlers are not supported when applying changes to a non-Oracle database.
`user_procedure`	A user-defined procedure that is invoked during apply for the specified operation on the specified object. If the procedure is a DML handler, then it is invoked instead of the default apply performed by Oracle. If the procedure is an error handler, then it is invoked when the apply process encounters an error. Specify `NULL` to unset a DML handler that is set for the specified operation on the specified object.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.
`apply_name`	The name of the apply process that uses the DML handler or error handler. If `NULL`, then sets the DML handler or error handler as a general handler for all apply processes in the database. If the `user_procedure` parameter is set to `NULL` to unset a handler, and the handler being unset is set for a specific apply process, then use the `apply_name` parameter to specify the apply process to unset the handler.

Usage Notes

Run this procedure at the destination database. The SET_DML_HANDLER procedure provides a way for users to apply logical change records containing DML changes (row LCRs) using a customized apply.

If the error_handler parameter is set to true, then it specifies that the user procedure is an error handler. An error handler is invoked only when a row LCR raises an apply process error. Such an error may result from a data conflict if no conflict handler is specified or if the update conflict handler cannot resolve the conflict. If the error_handler parameter is set to false, then the user procedure is a DML handler, not an error handler, and a DML handler is always run instead of performing the specified operation on the specified object.

This procedure either sets a DML handler or an error handler for a particular operation on an object. It cannot set both a DML handler and an error handler for the same object and operation.

If the apply_name parameter is non-NULL, then the DML handler or error handler is set for the specified apply process. In this case, this handler is not invoked for other apply processes at the local destination database. If the apply_name parameter is NULL, the default, then the handler is set as a general handler for all apply processes at the destination database. When a handler is set for a specific apply process, then this handler takes precedence over any general handlers. For example, consider the following scenario:

A DML handler named handler_hr is specified for an apply process named apply_hr for UPDATE operations on the hr.employees table.
A general DML handler named handler_gen also exists for UPDATE operations on the hr.employees table.

In this case, the apply_hr apply process uses the handler_hr DML handler for UPDATE operations on the hr.employees table.

At the source database, you must specify an unconditional supplemental log group for the columns needed by a DML or error handler.

Attention:

Do not modify LONG, LONG RAW or LOB column data in an LCR. This includes DML handlers, error handlers, and rule-based transformation functions.

Note:

Currently, setting an error handler for an apply process that is applying changes to a non-Oracle database is not supported.

The SET_DML_HANDLER procedure can be used to set either a DML handler or an error handler for row LCRs that perform a specified operation on a specified object. The following sections describe the signature of a DML handler procedure and the signature of an error handler procedure.

In either case, you must specify the full procedure name for the user_procedure parameter in one of the following forms:

[schema_name.]package_name.procedure_name
[schema_name.]procedure_name

If the procedure is in a package, then the package_name must be specified. The user who invokes the SET_DML_HANDLER procedure must have EXECUTE privilege on the specified procedure. Also, if the schema_name is not specified, then the user who invokes the SET_DML_HANDLER procedure is the default.

For example, suppose the procedure_name has the following properties:

hr is the schema_name.
apply_pkg is the package_name.
employees_default is the procedure_name.

In this case, specify the following:

hr.apply_pkg.employees_default

The following restrictions apply to the user procedure:

Do not execute COMMIT or ROLLBACK statements. Doing so may endanger the consistency of the transaction that contains the LCR.
If you are manipulating a row using the EXECUTE member procedure for the row LCR, then do not attempt to manipulate more than one row in a row operation. You must construct and execute manually any DML statements that manipulate more than one row.
If the command type is UPDATE or DELETE, then row operations resubmitted using the EXECUTE member procedure for the LCR must include the entire key in the list of old values. The key is the primary key or the smallest unique index that has at least one NOT NULL column, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure. If there is no specified key, then the key consists of all non LOB, non LONG, and non LONG RAW columns.
If the command type is INSERT, then row operations resubmitted using the EXECUTE member procedure for the LCR should include the entire key in the list of new values. Otherwise, duplicate rows are possible. The key is the primary key or the smallest unique index that has at least one NOT NULL column, unless a substitute key has been specified by the SET_KEY_COLUMNS procedure. If there is no specified key, then the key consists of all non LOB, non LONG, and non LONG RAW columns.

See Also:
Oracle Streams Replication Administrator's Guide for information about and restrictions regarding DML handlers and LOB, LONG, and LONG RAW datatypes

Signature of a DML Handler Procedure

The procedure specified in the user_procedure parameter must have the following signature:

PROCEDURE user_procedure (
parameter_name   IN  SYS.AnyData);

Here, user_procedure stands for the name of the procedure and parameter_name stands for the name of the parameter passed to the procedure. The parameter passed to the procedure is a SYS.AnyData encapsulation of a row LCR.

See Also:

Chapter 174, "Logical Change Record TYPEs" for more information about LCRs

Signature of an Error Handler Procedure

The procedure you create for error handling must have the following signature:

PROCEDURE user_procedure (
     message            IN  SYS.AnyData,
     error_stack_depth  IN  NUMBER,
     error_numbers      IN  DBMS_UTILITY.NUMBER_ARRAY,
     error_messages     IN  emsg_array);

If you want to retry the DML operation within the error handler, then have the error handler procedure run the EXECUTE member procedure for the LCR. The last error raised is on top of the error stack. To specify the error message at the top of the error stack, use error_numbers(1) and error_messages(1).

Note:

Each parameter is required and must have the specified datatype. However, you can change the names of the parameters.
The emsg_array value must be a user-defined array that is a table of type VARCHAR2 with at least 76 characters.

Running an error handler results in one of the following outcomes:

The error handler successfully resolves the error and returns control to the apply process.
The error handler fails to resolve the error, and the error is raised. The raised error causes the transaction to be rolled back and placed in the error queue.

SET_ENQUEUE_DESTINATION Procedure

This procedure sets the queue where an event that satisfies the specified rule is enqueued automatically by an apply process.

This procedure modifies the specified rule's action context to specify the queue. A rule action context is optional information associated with a rule that is interpreted by the client of the rules engine after the rule evaluates to TRUE for an event. In this case, the client of the rules engine is a Streams apply process. The information in an action context is an object of type SYS.RE$NV_LIST, which consists of a list of name-value pairs.

A queue destination specified by this procedure always consists of the following name-value pair in an action context:

The name is APPLY$_ENQUEUE.
The value is a SYS.AnyData instance containing the queue name specified as a VARCHAR2.

Syntax

DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
  rule_name               IN  VARCHAR2,
  destination_queue_name  IN  VARCHAR2);

Parameters

Table 15-12 SET_ENQUEUE_DESTINATION Procedure Parameters

Parameter Description

Parameter	Description
`rule_name`	The name of the rule, specified as `[``schema_name``.]``rule_name`. For example, to specify a rule named `hr5` in the `hr` schema, enter `hr.hr5` for this parameter. If the schema is not specified, then the current user is the default.
`destination_queue_name`	The name of the queue into which the apply process should enqueue the event. Specify the queue in the form `[``schema_name``.]``queue_name`. Only local queues can be specified. For example, to specify a queue in the `hr` schema named `streams_queue`, enter `hr.streams_queue`. If the schema is not specified, then the current user is the default. If `NULL`, then an existing name-value pair with the name `APPLY$_ENQUEUE` is removed. If no name-value pair exists with the name `APPLY$_ENQUEUE` for the rule, then no action is taken. If non-`NULL` and a name-value pair already exists for the rule with the name `APPLY$_ENQUEUE`, then it is removed, and a new name-value pair with the value specified by this parameter is added.

rule_name

The name of the rule, specified as [schema_name.]rule_name. For example, to specify a rule named hr5 in the hr schema, enter hr.hr5 for this parameter. If the schema is not specified, then the current user is the default.

destination_queue_name

The name of the queue into which the apply process should enqueue the event. Specify the queue in the form [schema_name.]queue_name. Only local queues can be specified.

For example, to specify a queue in the hr schema named streams_queue, enter hr.streams_queue. If the schema is not specified, then the current user is the default.

If NULL, then an existing name-value pair with the name APPLY$_ENQUEUE is removed. If no name-value pair exists with the name APPLY$_ENQUEUE for the rule, then no action is taken.

If non-NULL and a name-value pair already exists for the rule with the name APPLY$_ENQUEUE, then it is removed, and a new name-value pair with the value specified by this parameter is added.

Usage Notes

If an apply handler, such as a DML handler, DDL handler, or message handler, processes an event that is also enqueued into a destination queue, then the apply handler processes the event before it is enqueued.

The following are considerations for using this procedure:

This procedure does not verify that the specified queue exists. If the queue does not exist, then an error is raised when an apply process tries to enqueue an event into it.
Streams capture processes, propagations, and messaging clients ignore the action context created by this procedure.
The apply user of the apply processes using the specified rule must have the necessary privileges to enqueue events into the specified queue. If the queue is a secure queue, then the apply user must be a secure queue user of the queue.
The specified rule must be in the positive rule set for an apply process. If the rule is in the negative rule set for an apply process, then the apply process does not enqueue the event into the destination queue.
If the commit SCN for an event is less than or equal to the relevant instantiation SCN for the event, then the event is not enqueued into the destination queue, even if the event satisfies the apply process rule sets.

SET_EXECUTE Procedure

This procedure specifies whether an event that satisfies the specified rule is executed by an apply process.

This procedure modifies the specified rule's action context to specify event execution. A rule action context is optional information associated with a rule that is interpreted by the client of the rules engine after the rule evaluates to TRUE for an event. In this case, the client of the rules engine is a Streams apply process. The information in an action context is an object of type SYS.RE$NV_LIST, which consists of a list of name-value pairs.

An event execution directive specified by this procedure always consists of the following name-value pair in an action context:

The name is APPLY$_EXECUTE.
The value is a SYS.AnyData instance that contains NO as a VARCHAR2. When the value is NO, then an apply process does not execute the event and does not send the event to any apply handler.

Syntax

DBMS_APPLY_ADM.SET_EXECUTE(
  rule_name  IN  VARCHAR2,
  execute    IN  BOOLEAN);

Parameters

Table 15-13 SET_EXECUTE Procedure Parameters

Parameter Description

Parameter	Description
`rule_name`	The name of the rule, specified as `[``schema_name``.]``rule_name`. For example, to specify a rule named `hr5` in the `hr` schema, enter `hr.hr5` for this parameter. If the schema is not specified, then the current user is the default.
`execute`	If `true`, then removes the name-value pair with the name `APPLY$_EXECUTE` for the specified rule. Removing the name-value pair means that the apply process executes events that satisfy the rule. If no name-value pair with name `APPLY$_EXECUTE` exists for the rule, then no action is taken. If `false`, then adds a name-value pair to the rule's action context. The name is `APPLY$_EXECUTE` and the value is `NO`. An apply process does not execute an event that satisfies the rule and does not send the event to any apply handler. If a name-value pair already exists for the rule with the name `APPLY$_EXECUTE`, then it is removed, and a new one with the value `NO` is added. If `NULL`, then an error is raised.

rule_name

execute

If true, then removes the name-value pair with the name APPLY$_EXECUTE for the specified rule. Removing the name-value pair means that the apply process executes events that satisfy the rule. If no name-value pair with name APPLY$_EXECUTE exists for the rule, then no action is taken.

If false, then adds a name-value pair to the rule's action context. The name is APPLY$_EXECUTE and the value is NO. An apply process does not execute an event that satisfies the rule and does not send the event to any apply handler. If a name-value pair already exists for the rule with the name APPLY$_EXECUTE, then it is removed, and a new one with the value NO is added.

If NULL, then an error is raised.

Usage Notes

If the event is a logical change record (LCR) and the event is not executed, then the change encapsulated in the LCR is not made to the relevant local database object. Also, if the event is not executed, then it is not sent to any apply handler.

Note:

Streams capture processes, propagations, and messaging clients ignore the action context created by this procedure.
The specified rule must be in the positive rule set for an apply process for the apply process to follow the execution directive. If the rule is in the negative rule set for an apply process, then the apply process ignores the execution directive for the rule.

SET_GLOBAL_INSTANTIATION_SCN Procedure

This procedure records the specified instantiation SCN for the specified source database and, optionally, for the schemas at the source database and the tables owned by these schemas. This procedure overwrites any existing instantiation SCN for the database, and, if it sets the instantiation SCN for a schema or a table, then it overwrites any existing instantiation SCN for the schema or table.

This procedure gives you precise control over which DDL LCRs from a source database are ignored and which DDL LCRs are applied by an apply process.

Syntax

DBMS_APPLY_ADM.SET_GLOBAL_INSTANTIATION_SCN(
  source_database_name  IN  VARCHAR2,
  instantiation_scn     IN  NUMBER,
  apply_database_link   IN  VARCHAR2  DEFAULT NULL,
  recursive             IN  BOOLEAN   DEFAULT false);

Parameters

Table 15-14 SET_GLOBAL_INSTANTIATION_SCN Procedure Parameters

Parameter	Description
`source_database_name`	The global name of the source database. For example, `DBS1.NET`. If you do not include the domain name, then the local domain is appended to the database name automatically. For example, if you specify `DBS1` and the local domain is `.NET`, then `DBS1.NET` is specified automatically.
`instantiation_scn`	The instantiation SCN. Specify `NULL` to remove the instantiation SCN metadata for the source database from the data dictionary.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database of a local apply process is a non-Oracle database.
`recursive`	If `true`, then sets the instantiation SCN for the source database, all schemas in the source database, and all tables owned by the schemas in the source database. This procedure selects the schemas and tables from the `ALL_USERS` and `ALL_TABLES` data dictionary views, respectively, at the source database under the security context of the current user. If `false`, then sets the global instantiation SCN for the source database, but does not set the instantiation SCN for any schemas or tables Note: If `recursive` is set to `true`, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the current user. Also, a table must be accessible to the current user in either the `ALL_TABLES` or `DBA_TABLES` data dictionary view at the source database for this procedure to set the instantiation SCN for the table at the destination database.

Usage Notes

If the commit SCN of a DDL LCR for a database object from a source database is less than or equal to the instantiation SCN for that source database at a destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

The global instantiation SCN specified by this procedure is used for a DDL LCR only if the DDL LCR does not have object_owner, base_table_owner, and base_table_name specified. For example, the global instantiation SCN set by this procedure is used for DDL LCRs with a command_type of CREATE USER.

If the recursive parameter is set to true, then this procedure sets the instantiation SCN for each schema at a source database and for the tables owned by these schemas. This procedure uses the SET_SCHEMA_INSTANTIATION_SCN procedure to set the instantiation SCN for each schema, and it uses the SET_TABLE_INSTANTIATION_SCN procedure to set the instantiation SCN for each table. Each schema instantiation SCN is used for DDL LCRs on the schema, and each table instantiation SCN is used for DDL LCRs and row LCRs on the table.

If the recursive parameter is set to false, then this procedure does not set the instantiation SCN for any schemas or tables.

Note:

Any instantiation SCN specified by this procedure is used only for LCRs captured by a capture process. It is not used for user-created LCRs.
The instantiation SCN is not set for the SYS or SYSTEM schemas.

See Also:

"SET_SCHEMA_INSTANTIATION_SCN Procedure"
"SET_TABLE_INSTANTIATION_SCN Procedure"
"LCR$_DDL_RECORD Type" for more information about DDL LCRs
Oracle Streams Replication Administrator's Guide

SET_KEY_COLUMNS Procedures

This procedure records the set of columns to be used as the substitute primary key for apply purposes and removes existing substitute primary key columns for the specified object if they exist. Unlike true primary keys, these columns may contain NULLs.

Syntax

DBMS_APPLY_ADM.SET_KEY_COLUMNS(
   object_name          IN  VARCHAR2,
   { column_list        IN  VARCHAR2, | 
     column_table       IN  DBMS_UTILITY.NAME_ARRAY, } 
   apply_database_link  IN  VARCHAR2  DEFAULT NULL);

Note:

This procedure is overloaded. The column_list and column_table parameters are mutually exclusive.

Parameters

Table 15-15 SET_KEY_COLUMNS Procedure Parameters

Parameter	Description
`object_name`	The name of the table specified as `[``schema_name``.]``object_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default. If the apply process is applying changes to a non-Oracle database in a heterogeneous environment, then the object name is not verified.
`column_list`	A comma-delimited list of the columns in the table that you want to use as the substitute primary key, with no spaces between the column names. If the column_list parameter is empty or NULL, then the current set of key columns is removed.
`column_table`	A PL/SQL index-by table of type `DBMS_UTILITY.NAME_ARRAY` of the columns in the table that you want to use as the substitute primary key. The index for `column_table` must be 1-based, increasing, dense, and terminated by a `NULL`. If the `column_table` parameter is empty or `NULL`, then the current set of key columns is removed.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database.

Usage Notes

When not empty, this set of columns takes precedence over any primary key for the specified object. Do not specify substitute key columns if the object already has primary key columns and you want to use those primary key columns as the key.

Run this procedure at the destination database. At the source database, you must specify an unconditional supplemental log group for the substitute key columns.

Note:

Oracle recommends that each column you specify as a substitute key column be a NOT NULL column. You should also create a single index that includes all of the columns in a substitute key. Following these guidelines improves performance for updates, deletes, and piecewise updates to LOBs because Oracle can locate the relevant row more efficiently.
You should not permit applications to update the primary key or substitute key columns of a table. This ensures that Oracle can identify rows and preserve the integrity of the data.

If there is neither a primary key, nor a unique index that has at least one NOT NULL column, nor a substitute key for a table,then the key consists of all non LOB, non LONG, and non LONG RAW columns.

SET_PARAMETER Procedure

This procedure sets an apply parameter to the specified value.

Syntax

DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name  IN  VARCHAR2, 
   parameter   IN  VARCHAR2, 
   value       IN  VARCHAR2);

Parameters

Table 15-16 SET_PARAMETER Procedure Parameters

Parameter	Description
`apply_name`	The apply process name. Do not specify an owner.
`parameter`	The name of the parameter you are setting. See "Apply Process Parameters" for a list of these parameters.
`value`	The value to which the parameter is set

Apply Process Parameters

The following table lists the parameters for the apply process.

Table 15-17 Apply Process Parameters

Parameter Name	Possible Values	Default	Description
`commit_serialization`	`full` or `none`	`full`	The order in which applied transactions are committed. If `full`, then the apply process commits applied transactions in the order in which they were committed at the source database. If `none`, then the apply process may commit transactions may commit in any order. Performance is best if you specify `none`. Regardless of the specification, applied transactions may execute in parallel subject to data dependencies and constraint dependencies. Logical standby environments typically specify `full`.
`disable_on_error`	`Y` or `N`	`Y`	If `Y`, then the apply process is disabled on the first unresolved error, even if the error is not fatal. If `N`, then the apply process continues regardless of unresolved errors.
`disable_on_limit`	`Y` or `N`	`N`	If `Y`, then the apply process is disabled if the apply process terminates because it reached a value specified by the `time_limit` parameter or `transaction_limit` parameter. If `N`, then the apply process is restarted immediately after stopping because it reached a limit.
`maximum_scn`	A valid SCN or `infinite`	`infinite`	The apply process is disabled before applying a transaction with a commit SCN greater than or equal to the value specified. If `infinite`, then the apply process runs regardless of the SCN value.
`parallelism`	A positive integer	`1`	The number of transactions that may be concurrently applied Note: When you change the value of this parameter, the apply process is stopped and restarted automatically. This may take some time depending on the size of the transactions currently being applied. Setting the parallelism parameter to a number higher than the number of available parallel execution servers may disable the apply process. Make sure the `PROCESSES` and `PARALLEL_MAX_SERVERS` initialization parameters are set appropriately when you set the parallelism apply process parameter.
`time_limit`	A positive integer or `infinite`	`infinite`	The apply process stops as soon as possible after the specified number of seconds since it started. If `infinite`, then the apply process continues to run until it is stopped explicitly.
`trace_level`	`0` or a positive integer	`0`	Set this parameter only under the guidance of Oracle Support Services.
`transaction_limit`	A positive integer or `infinite`	`infinite`	The apply process stops after applying the specified number of transactions. If `infinite`, then the apply process continues to run regardless of the number of transactions applied.

Usage Notes

When you alter a parameter value, a short amount of time may pass before the new value for the parameter takes effect.

Note:

For all parameters that are interpreted as positive integers, the maximum possible value is 4,294,967,295. Where applicable, specify infinite for larger values.
For parameters that require an SCN setting, any valid SCN value can be specified.

SET_SCHEMA_INSTANTIATION_SCN Procedure

This procedure records the specified instantiation SCN for the specified schema in the specified source database and, optionally, for the tables owned by the schema at the source database. This procedure overwrites any existing instantiation SCN for the schema, and, if it sets the instantiation SCN for a table, it overwrites any existing instantiation SCN for the table.

This procedure gives you precise control over which DDL LCRs for a schema are ignored and which DDL LCRs are applied by an apply process.

Syntax

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
  source_schema_name    IN  VARCHAR2,
  source_database_name  IN  VARCHAR2,
  instantiation_scn     IN  NUMBER,
  apply_database_link   IN  VARCHAR2  DEFAULT NULL,
  recursive             IN  BOOLEAN   DEFAULT false);

Parameters

Table 15-18 SET_SCHEMA_INSTANTIATION_SCN Procedure Parameters

Parameter	Description
`source_schema_name`	The name of the source schema. For example, `hr`.
`source_database_name`	The global name of the source database. For example, `DBS1.NET`. If you do not include the domain name, then the local is appended to the database name automatically. For example, if you specify `DBS1` and the local domain is `.NET`, then `DBS1.NET` is specified automatically.
`instantiation_scn`	The instantiation SCN. Specify `NULL` to remove the instantiation SCN metadata for the source schema from the data dictionary.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database of a local apply process is a non-Oracle database.
`recursive`	If `true`, then sets the instantiation SCN for the specified schema and all tables owned by the schema in the source database. This procedure selects the tables owned by the specified schema from the `ALL_TABLES` data dictionary view at the source database under the security context of the current user. If `false`, then sets the instantiation SCN for specified schema, but does not set the instantiation SCN for any tables Note: If `recursive` is set to `true`, then a database link from the destination database to the source database is required. This database link must have the same name as the global name of the source database and must be accessible to the current user. Also, a table must be accessible to the current user in either the `ALL_TABLES` or `DBA_TABLES` data dictionary view at the source database for this procedure to set the instantiation SCN for the table at the destination database.

Usage Notes

If the commit SCN of a DDL LCR for a database object in a schema from a source database is less than or equal to the instantiation SCN for that database object at a destination database, then the apply process at the destination database disregards the DDL LCR. Otherwise, the apply process applies the DDL LCR.

The schema instantiation SCN specified by this procedure is used on the following types of DDL LCRs:

DDL LCRs with a command_type of CREATE TABLE
DDL LCRs with a non-NULL object_owner specified and no base_table_owner nor base_table_name specified.

For example, the schema instantiation SCN set by this procedure is used for a DDL LCR with a command_type of CREATE TABLE and ALTER USER.

The schema instantiation SCN specified by this procedure is not used for DDL LCRs with a command_type of CREATE USER. A global instantiation SCN is needed for such DDL LCRs.

If the recursive parameter is set to TRUE, then this procedure sets the table instantiation SCN for each table at the source database owned by the schema. This procedure uses the SET_TABLE_INSTANTIATION_SCN procedure to set the instantiation SCN for each table. Each table instantiation SCN is used for DDL LCRs and row LCRs on the table.

If the recursive parameter is set to false, then this procedure does not set the instantiation SCN for any tables.

Note:

Any instantiation SCN specified by this procedure is used only for LCRs captured by a capture process. It is not used for user-created LCRs.

See Also:

"SET_GLOBAL_INSTANTIATION_SCN Procedure"
"SET_TABLE_INSTANTIATION_SCN Procedure"
"LCR$_DDL_RECORD Type" for more information about DDL LCRs
Oracle Streams Replication Administrator's Guide

SET_TABLE_INSTANTIATION_SCN Procedure

This procedure records the specified instantiation SCN for the specified table in the specified source database. This procedure overwrites any existing instantiation SCN for the particular table.

This procedure gives you precise control over which LCRs for a table are ignored and which LCRs are applied by an apply process.

Syntax

DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
  source_object_name    IN  VARCHAR2,
  source_database_name  IN  VARCHAR2,
  instantiation_scn     IN  NUMBER,
  apply_database_link   IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 15-19 SET_TABLE_INSTANTIATION_SCN Procedure Parameters

Parameter	Description
`source_object_name`	The name of the source object specified as `[``schema_name``.]``object_name`. For example, `hr.employees`. If the schema is not specified, then the current user is the default.
`source_database_name`	The global name of the source database. For example, `DBS1.NET`. If you do not include the domain name, then the local domain name is appended to the database name automatically. For example, if you specify `DBS1` and the global domain is `.NET`, then `DBS1.NET` is specified automatically.
`instantiation_scn`	The instantiation SCN. Specify `NULL` to remove the instantiation SCN metadata for the source table from the data dictionary.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database of a local apply process is a non-Oracle database.

Usage Notes

If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at some destination database, then the apply process at the destination database disregards the LCR. Otherwise, the apply process applies the LCR.

The table instantiation SCN specified by this procedure is used on the following types of LCRs:

Row LCRs for the table
DDL LCRs that have a non-NULL base_table_owner and base_table_name specified, except for DDL LCRs with a command_type of CREATE TABLE

For example, the table instantiation SCN set by this procedure is used for DDL LCRs with a command_type of ALTER TABLE or CREATE TRIGGER.

Note:

The instantiation SCN specified by this procedure is used only for LCRs captured by a capture process. It is not used for user-created LCRs.

See Also:

"SET_GLOBAL_INSTANTIATION_SCN Procedure"
"SET_SCHEMA_INSTANTIATION_SCN Procedure"
"LCR$_ROW_RECORD Type" for more information about row LCRs
"LCR$_DDL_RECORD Type" for more information about DDL LCRs
Oracle Streams Replication Administrator's Guide

SET_UPDATE_CONFLICT_HANDLER Procedure

This procedure adds, modifies, or removes an update conflict handler for the specified object.

Syntax

DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
   object_name          IN  VARCHAR2,
   method_name          IN  VARCHAR2,
   resolution_column    IN  VARCHAR2,
   column_list          IN  DBMS_UTILITY.NAME_ARRAY,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL);

Parameters

Table 15-20 SET_UPDATE_CONFLICT_HANDLER Procedure Parameters

Parameter	Description
`object_name`	The schema and name of the table, specified as `[``schema_name``.]``object_name`, for which an update conflict handler is being added, modified, or removed. For example, if an update conflict handler is being added for table `employees` owned by user `hr`, then `specify` `hr.employees`. If the schema is not specified, then the current user is the default.
`method_name`	Type of update conflict handler to create. You can specify one of the built-in handlers, which determine whether the column list from the source database is applied for the row or whether the values in the row at the destination database are retained: `MAXIMUM`: Applies the column list from the source database if it has the greater value for the resolution column. Otherwise, retains the values at the destination database. `MINIMUM`: Applies the column list from the source database if it has the lesser value for the resolution column. Otherwise, retains the values at the destination database. `OVERWRITE`: Applies the column list from the source database, overwriting the column values at the destination database `DISCARD`: Retains the column list from the destination database, discarding the column list from the source database If `NULL`, then removes any existing update conflict handler with the same `object_name`, `resolution_column`, and `column_list`. If non-`NULL`, then replaces any existing update conflict handler with the same `object_name` and `resolution_column`.
`resolution_column`	Name of the column used to uniquely identify an update conflict handler. For the `MAXIMUM` and `MINIMUM` prebuilt methods, the resolution column is also used to resolve the conflict. The resolution column must be one of the columns listed in the `column_list` parameter. `NULL` is not allowed for this parameter. For the `OVERWRITE` and `DISCARD` prebuilt methods, you can any column in the column list.
`column_list`	List of columns for which the conflict handler is called. If a conflict occurs for one or more of the columns in the list when an apply process tries to apply a row LCR, then the conflict handler is called to resolve the conflict. The conflict handler is not called if a conflict occurs only for columns that are not in the list. Note: Prebuilt update conflict handlers do not support LOB, `LONG`, `LONG` `RAW`, and user-defined type columns. Therefore, you should not include these types of columns in the `column_list` parameter.
`apply_database_link`	The name of the database link to a non-Oracle database. This parameter should be set only when the destination database is a non-Oracle database. Note: Currently, conflict handlers are not supported when applying changes to a non-Oracle database.

Usage Notes

If you want to modify an existing update conflict handler, then you specify the table and resolution column of an the existing update conflict handler. You can modify the prebuilt method or the column list.

If you want to remove an existing update conflict handler, then specify NULL for the prebuilt method and specify the table, column list, and resolution column of the existing update conflict handler.

If an update conflict occurs, then Oracle completes the following series of actions:

Calls the appropriate update conflict handler to resolve the conflict
If no update conflict handler is specified or if the update conflict handler cannot resolve the conflict, then calls the appropriate error handler for the apply process, table, and operation to handle the error
If no error handler is specified or if the error handler cannot resolve the error, then raises an error and moves the transaction containing the row LCR that caused the error to the error queue

Note:
Currently, setting an update conflict handler for an apply process that is applying to a non-Oracle database is not supported.
See Also:

"Signature of an Error Handler Procedure" for information about setting an error handler

Oracle Streams Replication Administrator's Guide

Examples

The following is an example for setting an update conflict handler for the employees table in the hr schema:

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'salary';
  cols(2) := 'commission_pct';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'hr.employees',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'salary',
    column_list           =>  cols);
END;
/

This example sets a conflict handler that is called if a conflict occurs for the salary or commission_pct column in the hr.employees table. If such a conflict occurs, then the salary column is evaluated to resolve the conflict. If a conflict occurs only for a column that is not in the column list, such as the job_id column, then this conflict handler is not called.

START_APPLY Procedure

This procedure directs the apply process to start applying events.

Syntax

DBMS_APPLY_ADM.START_APPLY(
   apply_name  IN  VARCHAR2);

Parameter

Table 15-21 START_APPLY Procedure Parameter

Parameter	Description
`apply_name`	The apply process name. A `NULL` setting is not allowed. Do not specify an owner.

Usage Notes

The start status is persistently recorded. Hence, if the status is START, then the apply process is started upon database instance startup. Each apply process is an Oracle background process and is prefixed by AP.

The enqueue and dequeue state of DBMS_AQADM.START_QUEUE and DBMS_AQADM.STOP_QUEUE have no effect on the start status of an apply process.

You can create the apply process using the following procedures:

DBMS_APPLY_ADM.CREATE_APPLY
DBMS_STREAMS_ADM.ADD_GLOBAL_RULES
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
DBMS_STREAMS_ADM.ADD_TABLE_RULES
DBMS_STREAMS_ADM.ADD_SUBSET_RULES

See Also:
Chapter 96, "DBMS_STREAMS_ADM"

STOP_APPLY Procedure

This procedure stops the apply process from applying events and rolls back any unfinished transactions being applied.

Syntax

DBMS_APPLY_ADM.STOP_APPLY(
   apply_name  IN  VARCHAR2,
   force       IN  BOOLEAN   DEFAULT false);

Parameters

Table 15-22 STOP_APPLY Procedure Parameters

Parameter Description

apply_name

The apply process name. A NULL setting is not allowed. Do not specify an owner.

force

If true, then stops the apply process as soon as possible.

If false, then stops the apply process after ensuring that there are no gaps in the set of applied transactions.

The behavior of the apply process depends on the setting specified for the force parameter and the setting specified for the commit_serialization apply process parameter. See "Usage Notes" for more information.

Usage Notes

The stop status is persistently recorded. Hence, if the status is STOP, then the apply process is not started upon database instance startup.

The enqueue and dequeue state of DBMS_AQADM.START_QUEUE and DBMS_AQADM.STOP_QUEUE have no effect on the STOP status of an apply process.

The following table describes apply process behavior for each setting of the force parameter in the STOP_APPLY procedure and the commit_serialization apply process parameter. In all cases, the apply process rolls back any unfinished transactions when it stops.

force	commit_serialization	Apply Process Behavior
`true`	`full`	The apply process stops immediately and does not apply any unfinished transactions.
`true`	`none`	When the apply process stops, some transactions that have been applied locally may have committed at the source database at a later point in time than some transactions that have not been applied locally.
`false`	`full`	The apply process stops after applying the next uncommitted transaction in the commit order, if any such transaction is in progress.
`false`	`none`	Before stopping, the apply process applies all of the transactions that have a commit time that is earlier than the applied transaction with the most recent commit time.

For example, assume that the commit_serialization apply process parameter is set to none and there are three transactions: transaction 1 has the earliest commit time, transaction 2 is committed after transaction 1, and transaction 3 has the latest commit time. Also assume that an apply process has applied transaction 1 and transaction 3 and is in the process of applying transaction 2 when the STOP_APPLY procedure is run. Given this scenario, if the force parameter is set to true, then transaction 2 is not applied, and the apply process stops (transaction 2 is rolled back). If, however, the force parameter is set to false, then transaction 2 is applied before the apply process stops.

A different scenario would result if the commit_serialization apply process parameter is set to full. For example, assume that the commit_serialization apply process parameter is set to full and there are three transactions: transaction A has the earliest commit time, transaction B is committed after transaction A, and transaction C has the latest commit time. In this case, the apply process has applied transaction A and is in the process of applying transactions B and C when the STOP_APPLY procedure is run. Given this scenario, if the force parameter is set to true, then transactions B and C are not applied, and the apply process stops (transactions B and C are rolled back). If, however, the force parameter is set to false, then transaction B is applied before the apply process stops, and transaction C is rolled back.

See Also:

"SET_PARAMETER Procedure" for more information about the commit_serialization apply process parameter

15 DBMS_APPLY_ADM

Table 15-1 DBMS_APPLY_ADM Package Subprograms

Syntax

Parameters

Table 15-2 ALTER_APPLY Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-3 COMPARE_OLD_VALUES Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-4 CREATE_APPLY Procedure Parameters

Usage Notes

Syntax

Parameter

Table 15-5 DELETE_ALL_ERRORS Procedure Parameter

Syntax

Parameter

Table 15-6 DELETE_ERROR Procedure Parameter

Syntax

Parameters

Table 15-7 DROP_APPLY Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-8 EXECUTE_ALL_ERRORS Procedure Parameters

Syntax

Parameters

Table 15-9 EXECUTE_ERROR Procedure Parameters

Syntax

Parameters

Table 15-10 GET_ERROR_MESSAGE Function Parameters

Syntax

Parameters

Table 15-11 SET_DML_HANDLER Procedure Parameters

Usage Notes

Signature of a DML Handler Procedure

Signature of an Error Handler Procedure

Syntax

Parameters

Table 15-12 SET_ENQUEUE_DESTINATION Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-13 SET_EXECUTE Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-14 SET_GLOBAL_INSTANTIATION_SCN Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-15 SET_KEY_COLUMNS Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-16 SET_PARAMETER Procedure Parameters

Apply Process Parameters

Table 15-17 Apply Process Parameters

Usage Notes

Syntax

Parameters

Table 15-18 SET_SCHEMA_INSTANTIATION_SCN Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-19 SET_TABLE_INSTANTIATION_SCN Procedure Parameters

Usage Notes

Syntax

Parameters

Table 15-20 SET_UPDATE_CONFLICT_HANDLER Procedure Parameters

Usage Notes

Examples

Syntax

Parameter

Table 15-21 START_APPLY Procedure Parameter

Usage Notes

Syntax

Parameters

15
DBMS_APPLY_ADM