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

Usage Notes

The following usage notes apply to this procedure:

• Automatic Restart of Apply Components

• The ALTER_APPLY Procedure and XStream Outbound Servers

• The ALTER_APPLY Procedure and XStream Inbound Servers

Automatic Restart of Apply Components

An apply component 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

The ALTER_APPLY Procedure and XStream Outbound Servers

The following usage notes apply to this procedure and XStream outbound servers:

• The apply_user parameter can change the connect user for an outbound server.

• You cannot specify an apply handler for an outbound server. An outbound server ignores the settings for the following parameters: message_handler, ddl_handler, and precommit_handler.

  The client application can perform custom processing of the LCRs instead if necessary.

• An outbound server cannot set an apply tag for the changes it processes. An outbound server ignores the setting for the apply_tag parameter.

The ALTER_APPLY Procedure and XStream Inbound Servers

Inbound servers can use apply handlers and process only DML and DDL LCRs. Therefore, inbound servers ignore message handlers specified in the message_handler parameter.

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.

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

Syntax

DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
   object_name         IN VARCHAR2,
   column_list         IN VARCHAR2,
   operation           IN VARCHAR2 DEFAULT 'UPDATE',
   compare             IN BOOLEAN  DEFAULT TRUE,
   apply_database_link IN VARCHAR2 DEFAULT NULL);

DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
   object_name         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);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Conflict Detection

• The COMPARE_OLD_VALUES Procedure and XStream Outbound Servers

• The COMPARE_OLD_VALUES Procedure and XStream Inbound Servers

Conflict Detection

By default, an apply component uses the old column values in a row LCR to detect conflicts. You can choose not to compare old column values to avoid conflict detection for specific tables. For example, if you do not want to compare the old values for a set of columns during apply, then, using the COMPARE_OLD_VALUES procedure, specify the set of columns in the column_list or column_table parameter, and set the compare parameter to FALSE.

In addition, when the compare_key_only apply component parameter is set to Y, automatic conflict detection is disabled, and the apply component only uses primary key and unique key columns to identify the table row for a row LCR. When the compare_key_only apply component parameter is set to N, automatic conflict detection is enabled, and the apply component uses all of the old values in a row LCR to identify the table row for a row LCR.

The COMPARE_OLD_VALUES Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The COMPARE_OLD_VALUES Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

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

HEXTORAW('17')

Usage Notes

The following sections describe usage notes for this procedure:

• DBA Role Requirement

• Handler Procedure Names

• Message Handler and DDL Handler Procedure

• Precommit Handler Procedure

• The CREATE_APPLY Procedure and XStream Outbound Servers

• The CREATE_APPLY Procedure and XStream Inbound Servers

DBA Role Requirement

If the user who invokes this procedure is different from the user specified in the apply_user parameter, then the invoking user must be granted the DBA role. If the user who invokes this procedure is the same as the user specified in the apply_user parameter, then the DBA role is not required for the invoking user. Only the SYS user can set the apply_user to SYS.

Handler Procedure Names

For the message_handler, ddl_handler, and precommit_handler parameters, 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 handler procedure. Also, if the schema_name is not specified, then the user who invokes the CREATE_APPLY procedure is the default.

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  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 ANYDATA encapsulation of a user message. For the DDL handler procedure, the parameter passed to the procedure is a ANYDATA encapsulation of a DDL LCR.

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.

The CREATE_APPLY Procedure and XStream Outbound Servers

This procedure cannot create an XStream outbound server. To create an XStream outbound server, use the DBMS_XSTREAM_ADM package.

The CREATE_APPLY Procedure and XStream Inbound Servers

The following usage notes apply to this procedure and XStream inbound servers:

• The CREATE_APPLY procedure always creates an apply process. The apply process remains an apply process if it receives messages from a source other than an XStream client application, such as a capture process. The apply process can become an inbound server if an XStream client application attaches to it before it receives messages from any other source. After the initial contact, an apply process cannot be changed into an inbound server, and an inbound server cannot be changed into an apply process.

• When creating an inbound server using the CREATE_APPLY procedure, set the apply_captured parameter to TRUE. Inbound servers only process LCRs captured by a capture process.

• Inbound servers can use apply handlers. Inbound servers process only DML and DDL LCRs. Therefore, inbound servers ignore message handlers specified in the message_handler parameter.

An apply component schedules execution of transactions that involve the child object after all transactions with a lower commit system change number (commit SCN) that involve the parent object have been committed. An apply component uses the object identifier of the objects in the logical change records (LCRs) to detect dependencies. The apply component does not use column values in the LCRs to detect dependencies.

Syntax

DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
   object_name         IN  VARCHAR2,
   parent_object_name  IN  VARCHAR2);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• The CREATE_OBJECT_DEPENDENCY Procedure and XStream Outbound Servers

• The CREATE_OBJECT_DEPENDENCY Procedure and XStream Inbound Servers

The CREATE_OBJECT_DEPENDENCY Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The CREATE_OBJECT_DEPENDENCY Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

DBMS_APPLY_ADM.DELETE_ALL_ERRORS(
   apply_name  IN  VARCHAR2 DEFAULT NULL);

Parameter

Usage Notes

The following usage notes apply to this procedure:

• The DELETE_ALL_ERRORS Procedure and XStream Outbound Servers

• The DELETE_ALL_ERRORS Procedure and XStream Inbound Servers

The DELETE_ALL_ERRORS Procedure and XStream Outbound Servers

Outbound servers do not enqueue error transactions into an error queue. This procedure has no effect on XStream outbound servers.

The DELETE_ALL_ERRORS Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

DBMS_APPLY_ADM.DELETE_ERROR(
   local_transaction_id  IN  VARCHAR2);

Parameter

Usage Notes

The following usage notes apply to this procedure:

The DELETE_ERROR Procedure and XStream Outbound Servers

Outbound servers do not enqueue error transactions into an error queue. This procedure has no effect on XStream outbound servers.

The DELETE_ERROR Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

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

Parameters

Usage Notes

The following usage notes apply to this procedure:

• The DROP_APPLY Procedure and Rules

• The DROP_APPLY Procedure and XStream Outbound Servers

• The DROP_APPLY Procedure and XStream Inbound Servers

The DROP_APPLY Procedure and Rules

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

• ALL_STREAMS_GLOBAL_RULES

• DBA_STREAMS_GLOBAL_RULES

• ALL_STREAMS_SCHEMA_RULES

• DBA_STREAMS_SCHEMA_RULES

• ALL_STREAMS_TABLE_RULES

• DBA_STREAMS_TABLE_RULES

The DROP_APPLY Procedure and XStream Outbound Servers

When the DROP_APPLY procedure is executed on an outbound server, it runs the DROP_OUTBOUND procedure in the DBMS_XSTREAM_ADM package. Therefore, it might also drop the outbound server's capture process and queue.

The DROP_APPLY Procedure and XStream Inbound Servers

When the DROP_APPLY procedure is executed on an inbound server, it runs the DROP_INBOUND procedure in the DBMS_XSTREAM_ADM package. Therefore, it might also drop the inbound server's queue.

Syntax

  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
     object_name         IN  VARCHAR2,
     parent_object_name  IN  VARCHAR2);

Parameters

Usage Notes

The following usage notes apply to this procedure:

The DROP_OBJECT_DEPENDENCY Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The DROP_OBJECT_DEPENDENCY Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

The transactions are re-executed in commit SCN order. Error re-execution 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

Usage Notes

The following usage notes apply to this procedure:

• The EXECUTE_ALL_ERRORS Procedure and XStream Outbound Servers

• The EXECUTE_ALL_ERRORS Procedure and XStream Inbound Servers

The EXECUTE_ALL_ERRORS Procedure and XStream Outbound Servers

Outbound servers do not enqueue error transactions into an error queue. This procedure cannot be used with XStream outbound servers.

The EXECUTE_ALL_ERRORS Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

DBMS_APPLY_ADM.EXECUTE_ERROR(
   local_transaction_id  IN  VARCHAR2,
   execute_as_user       IN  BOOLEAN   DEFAULT FALSE,
   user_procedure        IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• The User Procedure

• The EXECUTE_ERROR Procedure and XStream Outbound Servers

• The EXECUTE_ERROR Procedure and XStream Inbound Servers

The User Procedure

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 EXECUTE_ERROR procedure must have EXECUTE privilege on the specified procedure. Also, if the schema_name is not specified, then the user who invokes the EXECUTE_ERROR procedure is the default.

For example, suppose the procedure_name has the following properties:

• strmadmin is the schema_name.

• fix_errors is the package_name.

• fix_hr_errors is the procedure_name.

In this case, specify the following:

strmadmin.fix_errors.fix_hr_errors

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

PROCEDURE user_procedure (
     in_anydata                    IN      ANYDATA,
     error_record                  IN      DBA_APPLY_ERROR%ROWTYPE,
     error_message_number          IN      NUMBER,
     messaging_default_processing  IN OUT  BOOLEAN,
     out_anydata                   OUT     ANYDATA);

The user procedure has the following parameters:

• in_anydata: The ANYDATA encapsulation of a message that the apply component passes to the procedure. A single transaction can include multiple messages. A message can be a row logical change record (row LCR), a DDL logical change record (DDL LCR), or a user message.

• error_record: The row in the DBA_APPLY_ERROR data dictionary view that identifies the transaction

• error_message_number: The message number of the ANYDATA object in the in_anydata parameter, starting at 1

• messaging_default_processing: If TRUE, then the apply component continues processing the message in the in_anydata parameter, which can include executing DML or DDL statements and invoking apply handlers.

  If FALSE, then the apply component skips processing the message in the in_anydata parameter and moves on to the next message in the in_anydata parameter.

• out_anydata: The ANYDATA object processed by the user procedure and used by the apply component if messaging_default_processing is TRUE.

If an LCR is executed using the EXECUTE LCR member procedure in the user procedure, then the LCR is executed directly, and the messaging_default_processing parameter should be set to FALSE. In this case, the LCR is not passed to any apply handlers.

Processing an error transaction with a user procedure results in one of the following outcomes:

• The user procedure modifies the transaction so that it can be executed successfully.

• The user procedure fails to make the necessary modifications, and an error is raised when transaction execution is attempted. In this case, the transaction is rolled back and remains in the error queue.

The following restrictions apply to the user procedure:

• Do not execute COMMIT or ROLLBACK statements. Doing so can endanger the consistency of the transaction.

• Do not modify LONG, LONG RAW or LOB column data in an LCR.

• If the ANYDATA object in the in_anydata parameter is a row LCR, then the out_anydata parameter must be row LCR if the messaging_default_processing parameter is set to TRUE.

• If the ANYDATA object in the in_anydata parameter is a DDL LCR, then the out_anydata parameter must be DDL LCR if the messaging_default_processing parameter is set to TRUE.

• The user who runs the user procedure must have the SELECT or READ privilege on the DBA_APPLY_ERROR data dictionary view.

The EXECUTE_ERROR Procedure and XStream Outbound Servers

Outbound servers do not enqueue error transactions into an error queue. This procedure cannot be used with XStream outbound servers.

The EXECUTE_ERROR Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

This function is overloaded. One version of this function contains two OUT parameters. These OUT parameters contain the destination queue into which the message should be enqueued, if one exists, and whether the message 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.

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 ANYDATA;

DBMS_APPLY_ADM.GET_ERROR_MESSAGE(
   message_number          IN   NUMBER, 
   local_transaction_id    IN   VARCHAR2)
RETURN ANYDATA;

Parameters

Usage Notes

The following usage notes apply to this procedure:

• The GET_ERROR_MESSAGE Procedure and XStream Outbound Servers

• The GET_ERROR_MESSAGE Procedure and XStream Inbound Servers

The GET_ERROR_MESSAGE Procedure and XStream Outbound Servers

Outbound servers do not enqueue error transactions into an error queue. This procedure cannot be used with XStream outbound servers.

The GET_ERROR_MESSAGE Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

DBMS_APPLY_ADM.HANDLE_COLLISIONS(
   apply_name    IN  VARCHAR2,
   enable        IN  BOOLEAN,
   object        IN  VARCHAR2,
   source_object IN  VARCHAR2 DEFAULT NULL);

Parameters

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

Syntax

DBMS_APPLY_ADM.SET_DML_CONFLICT_HANDLER(
  apply_name             IN  VARCHAR2,
  conflict_handler_name  IN  VARCHAR2,
  object                 IN  VARCHAR2  DEFAULT NULL,
  operation_name         IN  VARCHAR2  DEFAULT NULL,
  conflict_type          IN  VARCHAR2  DEFAULT NULL,
  method_name            IN  VARCHAR2  DEFAULT NULL,
  column_list            IN  VARCHAR2  DEFAULT NULL,
  resolution_column      IN  VARCHAR2  DEFAULT NULL,  
  source_object          IN  VARCHAR2  DEFAULT NULL);

DBMS_APPLY_ADM.SET_DML_CONFLICT_HANDLER(
  apply_name             IN  VARCHAR2,
  conflict_handler_name  IN  VARCHAR2,
  object                 IN  VARCHAR2  DEFAULT NULL,
  operation_name         IN  VARCHAR2  DEFAULT NULL,
  conflict_type          IN  VARCHAR2  DEFAULT NULL,
  method_name            IN  VARCHAR2  DEFAULT NULL,
  column_table           IN  DBMS_UTILITY.LNAME_ARRAY,
  resolution_column      IN  VARCHAR2  DEFAULT NULL,
  source_object          IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Modifying an Existing Conflict Handler

• Removing an Existing Conflict Handler

• Series of Actions for Conflicts

• Procedure DML Handlers for Conflicts

• A Column Can Be in Only One Column List

• The SET_DML_CONFLICT_HANDLER Procedure and XStream Outbound Servers

• The SET_DML_CONFLICT_HANDLER Procedure and XStream Inbound Servers

• Table 20-16

• Example

Modifying an Existing Conflict Handler

If you want to modify an existing conflict handler, then you specify the object, conflict_type, and resolution_column of an the existing conflict handler. You can modify the method_name or the column_list.

Removing an Existing Conflict Handler

If you want to remove an existing conflict handler, then specify NULL for the method_name and specify the object, conflict_type, and resolution_column of the existing conflict handler.

Series of Actions for Conflicts

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

1. Calls the appropriate conflict handler to resolve the conflict

2. If no conflict handler is specified or if the conflict handler cannot resolve the conflict, then calls the appropriate error handler for the apply component, object name, and operation name to handle the error

3. 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

   See Also:

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

Procedure DML Handlers for Conflicts

If you cannot use a prebuilt conflict handler to meet your requirements, then you can create a PL/SQL procedure to use as a custom conflict handler. You use the SET_DML_HANDLER procedure to designate one or more custom conflict handlers for a particular table. In addition, a custom conflict handler can process LOB columns and use LOB assembly.

A Column Can Be in Only One Column List

When a column is in a column list, and you try to add the same column to another column list, this procedure returns the following error:

ORA-00001: UNIQUE CONSTRAINT (SYS.APPLY$_CONF_HDLR_COLUMNS_UNQ1) VIOLATED

The SET_DML_CONFLICT_HANDLER Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The SET_DML_CONFLICT_HANDLER Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Example

The following is an example for setting a 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_DML_CONFLICT_HANDLER(
    apply_name            =>  'appl1',
    conflict_handler_name =>  'emp_handler_update',
    object                =>  'hr.employees',
    operation_name        =>  'UPDATE',
    conflict_type         =>  'ROW_EXISTS',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'salary',
    column_table          =>  cols);
END;
/

This example sets a conflict handler named emp_handler_update 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.

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,
   assemble_lobs        IN  BOOLEAN   DEFAULT TRUE);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Run the SET_DML_HANDLER Procedure at the Destination Database

• Procedure DML Handlers and Error Handlers

• The apply_name Parameter

• Signature of a DML Handler Procedure or Error Handler Procedure

• LOB Assembly

• The SET_DML_HANDLER Procedure and XStream Outbound Servers

• The SET_DML_HANDLER Procedure and XStream Inbound Servers

Run the SET_DML_HANDLER Procedure at the Destination Database

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.

Procedure DML Handlers and Error Handlers

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 error. Such an error can 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 procedure DML handler, not an error handler, and a procedure DML handler is always run instead of performing the specified operation on the specified object.

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

The apply_name Parameter

If the apply_name parameter is non-NULL, then the procedure DML handler or error handler is set for the specified apply component. In this case, this handler is not invoked for other apply components 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 components at the destination database. When a handler is set for a specific apply component, then this handler takes precedence over any general handlers. For example, consider the following scenario:

• A procedure DML handler named handler_hr is specified for an apply component named apply_hr for UPDATE operations on the hr.employees table.

• A general procedure DML handler named handler_gen also exists for UPDATE operations on the hr.employees table.

In this case, the apply_hr apply component uses the handler_hr procedure 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.

Signature of a DML Handler Procedure or Error Handler Procedure

You can use the SET_DML_HANDLER procedure to set either a procedure DML handler or an error handler for row LCRs that perform a specified operation on a specified object. The signatures of a DML handler procedure and of an error handler procedure are described following this section.

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 can 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 of the table columns, except for  columns of the following data types: LOB, LONG, LONG RAW, user-defined types (including object types, REFs, varrays, nested tables), and Oracle-supplied types (including Any types, XML types, spatial types, and media types).

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

PROCEDURE user_procedure (
   parameter_name  IN  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 ANYDATA encapsulation of a row LCR.

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

PROCEDURE user_procedure (
     message            IN  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).

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

• The error handler successfully resolves the error and returns control to the apply component.

• 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.

LOB Assembly

Do not modify LONG, LONG RAW, or nonassembled LOB column data in an LCR with procedure DML handlers, error handlers, or custom rule-based transformation functions. Procedure DML handlers and error handlers can modify LOB columns in row LCRs that have been constructed by LOB assembly.

The SET_DML_HANDLER Procedure and XStream Outbound Servers

Outbound servers ignore all apply handlers. This procedure has no effect on XStream outbound servers.

The SET_DML_HANDLER Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

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 a message. In this case, the client of the rules engine is an apply component. 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 an 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

Usage Notes

The following usage notes apply to this procedure:

• The SET_ENQUEUE_DESTINATION Procedure and Apply Handlers

• Considerations for the SET_ENQUEUE_DESTINATION Procedure

• The SET_ENQUEUE_DESTINATION Procedure and XStream Outbound Servers

• The SET_ENQUEUE_DESTINATION Procedure and XStream Inbound Servers

The SET_ENQUEUE_DESTINATION Procedure and Apply Handlers

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

Considerations for the SET_ENQUEUE_DESTINATION Procedure

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 component tries to enqueue a message into it.

• Oracle Replication capture processes, propagations, and messaging clients ignore the action context created by this procedure.

• The apply user of the apply component using the specified rule must have the necessary privileges to enqueue messages 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 component. If the rule is in the negative rule set for an apply component, then the apply component does not enqueue the message into the destination queue.

• If the commit SCN for a message is less than or equal to the relevant instantiation SCN for the message, then the message is not enqueued into the destination queue, even if the message satisfies the apply component rule sets.

The SET_ENQUEUE_DESTINATION Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The SET_ENQUEUE_DESTINATION Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

This procedure modifies the specified rule's action context to specify message 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 a message. In this case, the client of the rules engine is an apply component. 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 message 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 an ANYDATA instance that contains NO as a VARCHAR2. When the value is NO, an apply component does not execute the message and does not send the message to any apply handler.

Syntax

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

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Considerations for the SET_EXECUTE Procedure

• The SET_EXECUTE Procedure and XStream Outbound Servers

• The SET_EXECUTE Procedure and XStream Inbound Servers

Considerations for the SET_EXECUTE Procedure

The following are considerations for using this procedure:

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

• Oracle Replication 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 component for the apply component to follow the execution directive. If the rule is in the negative rule set for an apply component, then the apply component ignores the execution directive for the rule.

The SET_EXECUTE Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The SET_EXECUTE Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

This procedure gives you precise control over which DDL logical change records (DDL LCRs) from a source database are ignored and which DDL LCRs are applied by an apply component.

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,
  source_root_name      IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Instantiation SCNs and DDL LCRs

• The recursive Parameter

• Considerations for the SET_GLOBAL_INSTANTIATION_SCN Procedure

• The SET_GLOBAL_INSTANTIATION_SCN Procedure and XStream Outbound Servers

• The SET_GLOBAL_INSTANTIATION_SCN Procedure and XStream Inbound Servers

• The SET_GLOBAL_INSTANTIATION_SCN Procedure and CDBs

Instantiation SCNs and DDL LCRs

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 component at the destination database disregards the DDL LCR. Otherwise, the apply component 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.

The recursive Parameter

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.

Considerations for the SET_GLOBAL_INSTANTIATION_SCN Procedure

The following are considerations for using this procedure:

• 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.

The SET_GLOBAL_INSTANTIATION_SCN Procedure and XStream Outbound Servers

Instantiation SCNs are not required for database objects processed by an outbound server. If an instantiation SCN is set for a database object, then the outbound server only sends the LCRs for the database object with SCN values that are greater than the instantiation SCN value. If a database object does not have an instantiation SCN set, then the outbound server skips the instantiation SCN check and sends all LCRs for that database object. In both cases, the outbound server only sends LCRs that satisfy its rule sets.

The apply_database_link parameter must be set to NULL or to the local database for this procedure to set an instantiation SCN for an outbound server.

The SET_GLOBAL_INSTANTIATION_SCN Procedure and XStream Inbound Servers

Inbound servers ignore instantiation SCNs. This procedure has no effect on XStream inbound servers.

The SET_GLOBAL_INSTANTIATION_SCN Procedure and CDBs

In a CDB, this procedure must be invoked from the same container as the apply process that uses the instantiation SCN information.

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

Syntax

DBMS_APPLY_ADM.SET_KEY_COLUMNS(
   object_name          IN  VARCHAR2,
   column_list          IN  VARCHAR2,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL,
   apply_name           IN  VARCHAR2  DEFAULT NULL);

DBMS_APPLY_ADM.SET_KEY_COLUMNS(
   object_name          IN  VARCHAR2,
   column_table         IN  DBMS_UTILITY.NAME_ARRAY,
   apply_database_link  IN  VARCHAR2  DEFAULT NULL,
   apply_name           IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Considerations for the SET_KEY_COLUMNS Procedure

• Duplicate Rows and Substitute Primary Key Columns

• The SET_KEY_COLUMNS Procedure and XStream Outbound Servers

• The SET_KEY_COLUMNS Procedure and XStream Inbound Servers

• The SET_KEY_COLUMNS Procedure and CDBs

Considerations for the SET_KEY_COLUMNS Procedure

The following are considerations for using this procedure:

• When not empty, the specified set of columns takes precedence over any primary key for the specified object. Do not specify substitute key columns if the object 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.

• Unlike true primary keys, columns specified as substitute key column columns can contain NULLs. However, Oracle recommends that each column you specify as a substitute key column be a NOT NULL column. You also should 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.

• Do 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 of the table columns, except for columns of the following data types: LOB, LONG, LONG RAW, user-defined types (including object types, REFs, varrays, nested tables), and Oracle-supplied types (including Any types, XML types, spatial types, and media types).

Duplicate Rows and Substitute Primary Key Columns

A table has duplicate rows when all of the column values are identical for two or more rows in the table, excluding LOB, LONG, and LONG RAW columns. You can specify substitute primary key columns for a table at a destination database using by the SET_KEY_COLUMNS procedure. When substitute primary key columns are specified for a table with duplicate rows at a destination database, and the allow_duplicate_rows apply component parameter is set to Y, meet the following requirements to keep the table data synchronized at the source and destination databases:

• Ensure that supplemental logging is specified at source database for the columns specified as substitute key columns at the destination database. The substitute key columns must be in an unconditional log group at the source database.

• Ensure that the substitute key columns uniquely identify each row in the table at the destination database.

The rest of this section provides more details about these requirements.

When there is no key for a table and the allow_duplicate_rows apply component parameter is set to Y, a single row LCR with an UPDATE or DELETE command type only is applied to one of the duplicate rows. In this case, if the table at the source database and the table at the destination database have corresponding duplicate rows, then a change that changes all of the duplicate rows at the source database also changes all the duplicate rows at the destination database when the row LCRs resulting from the change are applied.

For example, suppose a table at a source database has two duplicate rows. An update is performed on the duplicate rows, resulting in two row LCRs. At the destination database, one row LCR is applied to one of the duplicate rows. At this point, the rows are no longer duplicate at the destination database because one of the rows has changed. When the second row LCR is applied at the destination database, the rows are duplicate again. Similarly, if a delete is performed on these duplicate rows at the source database, then both rows are deleted at the destination database when the row LCRs resulting from the source change are applied.

When substitute primary key columns are specified for a table, row LCRs are identified with rows in the table during apply using the substitute primary key columns. If substitute primary key columns are specified for a table with duplicate rows at a destination database, and the allow_duplicate_rows apply component parameter is set to Y, then an update performed on duplicate rows at the source database can result in different changes when the row LCRs are applied at the destination database. Specifically, if the update does not change one of the columns specified as a substitute primary key column, then the same duplicate row can be updated multiple times at the destination database, while other duplicate rows might not be updated.

Also, if the substitute key columns do not identify each row in the table at the destination database uniquely, then a row LCR identified with multiple rows can update any one of the rows. In this case, the update in the row LCR might not be applied to the correct row in the table at the destination database.

An apply component ignores substitute primary key columns when it determines whether rows in a table are duplicates. An apply component determines that rows are duplicates only if all of the column values in the rows are identical (excluding LOB, LONG, and LONG RAW columns). Therefore, an apply component always raises an error if a single update or delete changes two or more nonduplicate rows in a table.

For example, consider a table with columns c1, c2, and c3 on which the SET_KEY_COLUMNS procedure is used to designate column c1 as the substitute primary key. If two rows have the same key value for the c1 column, but different value for the c2 or c3 columns, then an apply component does not treat the rows as duplicates. If an update or delete modifies more than one row because the c1 values in the rows are the same, then the apply component raises an error regardless of the setting for the allow_duplicate_rows apply component parameter.

The SET_KEY_COLUMNS Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The SET_KEY_COLUMNS Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

The SET_KEY_COLUMNS Procedure and CDBs

This procedure defines the columns that are used as a substitute primary key. You must perform the SET_KEY_COLUMNS procedure in the appropriate PDB.

Syntax

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

Parameters

Apply Component Parameters

The following table lists the parameters for an apply component.

Usage Notes

The following usage notes apply to this procedure:

• Delays Are Possible Before New Parameter Settings Take Effect

• Parameters Interpreted as Positive Integers

• Parameters with a System Change Number (SCN) Setting

• The SET_PARAMETER Procedure and Replication

• The SET_PARAMETER Procedure and XStream Outbound Servers

• The SET_PARAMETER Procedure and XStream Inbound Servers

Delays Are Possible Before New Parameter Settings Take Effect

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

Parameters Interpreted as Positive Integers

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.

Parameters with a System Change Number (SCN) Setting

For parameters that require an SCN setting, any valid SCN value can be specified.

The SET_PARAMETER Procedure and Replication

You can use the following parameters in Replication if you enable XStream performance optimizations for Oracle Replication using the procedure DBMS_XSTREAM_ADM.ENABLE_GG_XSTREAM_FOR_STREAMS:

• apply_sequence_nextval

• compute_lcr_dep_on_arrival

• eager_size

• grouptransops

• max_parallelism

• max_sga_size

• optimize_progress_table

• parallelism_interval

The SET_PARAMETER Procedure and XStream Outbound Servers

Outbound servers ignore the settings for the following apply parameters:

• allow_duplicate_rows

• commit_serialization

• compare_key_only

• compute_lcr_dep_on_arrival

• disable_on_error

• eager_size

• enable_xstream_table_stats

• grouptransops

• handlecollisions

• optimize_self_updates

• parallelism

• parallelism_interval

• preserve_encryption

• rtrim_on_implicit_conversion

• suppresstriggers

The commit_serialization parameter is always set to FULL for an outbound server, and the parallelism parameter is always set to 1 for an outbound server.

You can use the other apply parameters with outbound servers.

The SET_PARAMETER Procedure and XStream Inbound Servers

Inbound servers ignore the settings for the following apply parameters:

• excludetag

• excludetrans

• excludeuser

• excludeuserid

• getapplops

• getreplicates

• ignore_transaction

• maximum_scn

You can use all of the other apply component parameters with inbound servers.

The default setting for the compare_key_only parameter for an inbound server is Y.

The default setting for the parallelism parameter for an inbound server is 4.

You can choose between several predefined actions for a given error.

Syntax

DBMS_APPLY_ADM.SET_REPERROR_HANDLER(
  apply_name     IN  VARCHAR2,
  object         IN  VARCHAR2,
  error_number   IN  NUMBER,
  method         IN  VARCHAR2,
  source_object  IN  VARCHAR2  DEFAULT NULL,
  max_retries    IN  NUMBER    DEFAULT NULL,
  delay_csecs    IN  NUMBER    DEFAULT 6000);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Priority of Error Handlers

Priority of Error Handlers

Any conflict handling specified by SET_UPDATE_CONFLICT_HANDLER or SET_DML_CONFLICT_HANDLER is tried before the actions specified by SET_REPERROR_HANDLER. The PL/SQL procedure specified by SET_DML_HANDLER is called to handle the error if none of the previously mentioned methods resolve it.

This procedure gives you precise control over which DDL logical change records (LCRs) for a schema are ignored and which DDL LCRs are applied by an apply component.

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,
  source_root_name      IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• The SET_SCHEMA_INSTANTIATION_SCN Procedure and LCRs

• Instantiation SCNs and DDL LCRs

• The recursive Parameter

• The SET_SCHEMA_INSTANTIATION_SCN Procedure and XStream Outbound Servers

• The SET_SCHEMA_INSTANTIATION_SCN Procedure and XStream Inbound Servers

• The SET_SCHEMA_INSTANTIATION_SCN Procedure and CDBs

The SET_SCHEMA_INSTANTIATION_SCN Procedure and LCRs

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.

Instantiation SCNs and DDL LCRs

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 component at the destination database disregards the DDL LCR. Otherwise, the apply component 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 neither 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.

The recursive Parameter

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.

The SET_SCHEMA_INSTANTIATION_SCN Procedure and XStream Outbound Servers

Instantiation SCNs are not required for database objects processed by an outbound server. If an instantiation SCN is set for a database object, then the outbound server only sends the LCRs for the database object with SCN values that are greater than the instantiation SCN value. If a database object does not have an instantiation SCN set, then the outbound server skips the instantiation SCN check and sends all LCRs for that database object. In both cases, the outbound server only sends LCRs that satisfy its rule sets.

The apply_database_link parameter must be set to NULL or to the local database for this procedure to set an instantiation SCN for an outbound server.

The SET_SCHEMA_INSTANTIATION_SCN Procedure and XStream Inbound Servers

Inbound servers ignore instantiation SCNs. This procedure has no effect on XStream inbound servers.

The SET_SCHEMA_INSTANTIATION_SCN Procedure and CDBs

In a CDB, this procedure must be invoked from the same container as the apply process that uses the instantiation SCN information.

This procedure gives you precise control over which logical change records (LCRs) for a table are ignored and which LCRs are applied by an apply component.

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,
  source_root_name      IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Instantiation SCNs and LCRs

• The SET_TABLE_INSTANTIATION_SCN Procedure and XStream Outbound Servers

• The SET_TABLE_INSTANTIATION_SCN Procedure and XStream Inbound Servers

• The SET_TABLE_INSTANTIATION_SCN Procedure and CDBs

Instantiation SCNs and LCRs

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 component at the destination database disregards the LCR. Otherwise, the apply component 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.

The SET_TABLE_INSTANTIATION_SCN Procedure and XStream Outbound Servers

Instantiation SCNs are not required for database objects processed by an outbound server. If an instantiation SCN is set for a database object, then the outbound server only sends the LCRs for the database object with SCN values that are greater than the instantiation SCN value. If a database object does not have an instantiation SCN set, then the outbound server skips the instantiation SCN check and sends all LCRs for that database object. In both cases, the outbound server only sends LCRs that satisfy its rule sets.

The apply_database_link parameter must be set to NULL or to the local database for this procedure to set an instantiation SCN for an outbound server.

The SET_TABLE_INSTANTIATION_SCN Procedure and XStream Inbound Servers

Inbound servers ignore instantiation SCNs. This procedure has no effect on XStream inbound servers.

The SET_TABLE_INSTANTIATION_SCN Procedure and CDBs

In a CDB, this procedure must be invoked from the same container as the apply process that uses the instantiation SCN information.

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

Usage Notes

The following usage notes apply to this procedure:

• Modifying an Existing Update Conflict Handler

• Removing an Existing Update Conflict Handler

• Series of Actions for Conflicts

• Procedure DML Handlers for Conflicts

• A Column Can Be in Only One Column List

• Update Conflict Handlers and Non-Oracle Databases

• The SET_UPDATE_CONFLICT_HANDLER Procedure and XStream Outbound Servers

• The SET_UPDATE_CONFLICT_HANDLER Procedure and XStream Inbound Servers

Modifying an Existing Update Conflict Handler

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.

Removing an Existing Update Conflict Handler

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.

Series of Actions for Conflicts

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

1. Calls the appropriate update conflict handler to resolve the conflict

2. 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 component, table, and operation to handle the error

3. 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

   See Also:

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

Procedure DML Handlers for Conflicts

If you cannot use a prebuilt update conflict handler to meet your requirements, then you can create a PL/SQL procedure to use as a custom conflict handler. You use the SET_DML_HANDLER procedure to designate one or more custom conflict handlers for a particular table. In addition, a custom conflict handler can process LOB columns and use LOB assembly.

A Column Can Be in Only One Column List

When a column is in a column list, and you try to add the same column to another column list, this procedure returns the following error:

ORA-00001: UNIQUE CONSTRAINT (SYS.APPLY$_CONF_HDLR_COLUMNS_UNQ1) VIOLATED

Update Conflict Handlers and Non-Oracle Databases

Setting an update conflict handler for an apply component that is applying to a non-Oracle database is not supported.

The SET_UPDATE_CONFLICT_HANDLER Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The SET_UPDATE_CONFLICT_HANDLER Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

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.

An apply component uses the name of a value dependencies to detect dependencies between row logical change records (row LCRs) that contain the columns defined in the value dependency. Value dependencies can define virtual foreign key relationships between tables, but, unlike foreign key relationships, value dependencies can involve more than two database objects.

This procedure is overloaded. The attribute_list and attribute_table parameters are mutually exclusive.

Syntax

DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
   dependency_name IN VARCHAR2,
   object_name     IN VARCHAR2,
   attribute_list  IN VARCHAR2);

DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
   dependency_name IN VARCHAR2,
   object_name     IN VARCHAR2,
   attribute_table IN DBMS_UTILITY.NAME_ARRAY);

Parameters

Usage Notes

The following usage notes apply to this procedure:

• The SET_VALUE_DEPENDENCY Procedure and XStream Outbound Servers

• The SET_VALUE_DEPENDENCY Procedure and XStream Inbound Servers

The SET_VALUE_DEPENDENCY Procedure and XStream Outbound Servers

This procedure has no effect on XStream outbound servers.

The SET_VALUE_DEPENDENCY Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

DBMS_APPLY_ADM.START_APPLY(
   apply_name  IN  VARCHAR2);

Parameter

Usage Notes

The following usage notes apply to this procedure:

• Apply Component Status

• The START_APPLY Procedure and XStream Outbound Servers

• The START_APPLY Procedure and XStream Inbound Servers

Apply Component Status

The apply component status is persistently recorded. Hence, if the status is ENABLED, then the apply component is started upon database instance startup. An apply component (annn) is an Oracle background process. 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 component.

The START_APPLY Procedure and XStream Outbound Servers

This procedure functions the same way for apply processes and outbound servers.

The START_APPLY Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.

Syntax

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

Parameters

Usage Notes

The following usage notes apply to this procedure:

• Apply Component Status

• Queue Subprograms Have No Effect on Apply Component Status

• The STOP_APPLY force Parameter and the commit_serialization Apply Parameter

• The STOP_APPLY Procedure and XStream Outbound Servers

• The STOP_APPLY Procedure and XStream Inbound Servers

Apply Component Status

The apply component status is persistently recorded. Hence, if the status is DISABLED or ABORTED, then the apply component is not started upon database instance startup.

Queue Subprograms Have No Effect on Apply Component Status

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 component.

The STOP_APPLY force Parameter and the commit_serialization Apply Parameter

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

For example, assume that the commit_serialization apply component parameter is set to DEPENDENT_TRANSACTIONS 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 component 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 component stops (transaction 2 is rolled back). If, however, the force parameter is set to FALSE, then transaction 2 is applied before the apply component stops.

A different scenario would result if the commit_serialization apply component parameter is set to FULL. For example, assume that the commit_serialization apply component 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 component 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 component 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 component stops, and transaction C is rolled back.

The STOP_APPLY Procedure and XStream Outbound Servers

This procedure functions the same way for apply processes and outbound servers.

The STOP_APPLY Procedure and XStream Inbound Servers

This procedure functions the same way for apply processes and inbound servers.