Managing Oracle Streams Information Consumption

17.1 Starting an Apply Process

You run the START_APPLY procedure in the DBMS_APPLY_ADM package to start an existing apply process. For example, the following procedure starts an apply process named strm01_apply:

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'strm01_apply');
END;
/

See Also:

The Oracle Enterprise Manager Cloud Control online help for instructions about starting an apply process with Oracle Enterprise Manager Cloud Control

17.2 Stopping an Apply Process

You run the STOP_APPLY procedure in the DBMS_APPLY_ADM package to stop an existing apply process. For example, the following procedure stops an apply process named strm01_apply:

BEGIN
  DBMS_APPLY_ADM.STOP_APPLY(
    apply_name => 'strm01_apply');
END;
/

See Also:

The Oracle Enterprise Manager Cloud Control online help for instructions about stopping an apply process with Oracle Enterprise Manager Cloud Control

17.3 Managing the Rule Set for an Apply Process

This section contains instructions for completing the following tasks:

See Also:

17.3.1 Specifying the Rule Set for an Apply Process

You can specify one positive rule set and one negative rule set for an apply process. The apply process applies a message if it evaluates to TRUE for at least one rule in the positive rule set and discards a message if it evaluates to TRUE for at least one rule in the negative rule set. The negative rule set is evaluated before the positive rule set.

17.3.1.1 Specifying a Positive Rule Set for an Apply Process

You specify an existing rule set as the positive rule set for an existing apply process using the rule_set_name parameter in the ALTER_APPLY procedure. This procedure is in the DBMS_APPLY_ADM package.

For example, the following procedure sets the positive rule set for an apply process named strm01_apply to strm02_rule_set.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name    => 'strm01_apply',
    rule_set_name => 'strmadmin.strm02_rule_set');
END;
/

17.3.1.2 Specifying a Negative Rule Set for an Apply Process

You specify an existing rule set as the negative rule set for an existing apply process using the negative_rule_set_name parameter in the ALTER_APPLY procedure. This procedure is in the DBMS_APPLY_ADM package.

For example, the following procedure sets the negative rule set for an apply process named strm01_apply to strm03_rule_set.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name             => 'strm01_apply',
    negative_rule_set_name => 'strmadmin.strm03_rule_set');
END;
/

17.3.2 Adding Rules to the Rule Set for an Apply Process

To add rules to the rule set for an apply process, you can run one of the following procedures:

Excluding the ADD_SUBSET_RULES procedure, these procedures can add rules to the positive rule set or negative rule set for an apply process. The ADD_SUBSET_RULES procedure can add rules only to the positive rule set for an apply process.

See Also:

"System-Created Rules"

17.3.2.1 Adding Rules to the Positive Rule Set for an Apply Process

The following example runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the positive rule set of an apply process named strm01_apply:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name       => 'hr.departments',
    streams_type     => 'apply',
    streams_name     => 'strm01_apply',
    queue_name       => 'streams_queue',
    include_dml      => TRUE,
    include_ddl      => TRUE,
    source_database  => 'dbs1.example.com',
    inclusion_rule   => TRUE);
END;
/

Running this procedure performs the following actions:

Creates one rule that evaluates to TRUE for row LCRs that contain the results of DML changes to the hr.departments table. The rule name is system generated.
Creates one rule that evaluates to TRUE for DDL LCRs that contain DDL changes to the hr.departments table. The rule name is system generated.
Specifies that both rules evaluate to TRUE only for LCRs whose changes originated at the dbs1.example.com source database.
Adds the rules to the positive rule set associated with the apply process because the inclusion_rule parameter is set to TRUE.

17.3.2.2 Adding Rules to the Negative Rule Set for an Apply Process

The following example runs the ADD_TABLE_RULES procedure in the DBMS_STREAMS_ADM package to add rules to the negative rule set of an apply process named strm01_apply:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name       => 'hr.regions',
    streams_type     => 'apply',
    streams_name     => 'strm01_apply',
    queue_name       => 'streams_queue',
    include_dml      => TRUE,
    include_ddl      => TRUE,
    source_database  => 'dbs1.example.com',
    inclusion_rule   => FALSE);
END;
/

Running this procedure performs the following actions:

Creates one rule that evaluates to TRUE for row LCRs that contain the results of DML changes to the hr.regions table. The rule name is system generated.
Creates one rule that evaluates to TRUE for DDL LCRs that contain DDL changes to the hr.regions table. The rule name is system generated.
Specifies that both rules evaluate to TRUE only for LCRs whose changes originated at the dbs1.example.com source database.
Adds the rules to the negative rule set associated with the apply process because the inclusion_rule parameter is set to FALSE.

17.3.3 Removing a Rule from the Rule Set for an Apply Process

You remove a rule from a rule set for an existing apply process by running the REMOVE_RULE procedure in the DBMS_STREAMS_ADM package. For example, the following procedure removes a rule named departments3 from the positive rule set of an apply process named strm01_apply.

BEGIN
  DBMS_STREAMS_ADM.REMOVE_RULE(
    rule_name        => 'departments3',
    streams_type     => 'apply',
    streams_name     => 'strm01_apply',
    drop_unused_rule => TRUE,
    inclusion_rule   => TRUE);
END;
/

In this example, the drop_unused_rule parameter in the REMOVE_RULE procedure is set to TRUE, which is the default setting. Therefore, if the rule being removed is not in any other rule set, then it will be dropped from the database. If the drop_unused_rule parameter is set to FALSE, then the rule is removed from the rule set, but it is not dropped from the database even if it is not in any other rule set.

If the inclusion_rule parameter is set to FALSE, then the REMOVE_RULE procedure removes the rule from the negative rule set for the apply process, not from the positive rule set.

To remove all of the rules in a rule set for the apply process, then specify NULL for the rule_name parameter when you run the REMOVE_RULE procedure.

17.3.4 Removing a Rule Set for an Apply Process

You remove a rule set from an existing apply process using the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. This procedure can remove the positive rule set, negative rule set, or both. Specify TRUE for the remove_rule_set parameter to remove the positive rule set for the apply process. Specify TRUE for the remove_negative_rule_set parameter to remove the negative rule set for the apply process.

For example, the following procedure removes both the positive and negative rule sets from an apply process named strm01_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name               => 'strm01_apply',
    remove_rule_set          => TRUE,
    remove_negative_rule_set => TRUE);
END;
/

Note:

If an apply process that dequeues messages from a buffered queues does not have a positive or negative rule set, then the apply process dequeues all captured LCRs in its queue. Similarly, if an apply process that dequeues messages from a persistent queue does not have a positive or negative rule set, the apply process dequeues all persistent LCRs and persistent user messages in its queue.

17.4 Setting an Apply Process Parameter

Set an apply process parameter using the SET_PARAMETER procedure in the DBMS_APPLY_ADM package. Apply process parameters control the way an apply process operates.

For example, the following procedure sets the commit_serialization parameter for an apply process named strm01_apply to DEPENDENT_TRANSACTIONS. This setting for the commit_serialization parameter enables the apply process to commit transactions in any order.

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name   => 'strm01_apply',
    parameter    => 'commit_serialization',
    value        => 'DEPENDENT_TRANSACTIONS');
END;
/

Note:

The value parameter is always entered as a VARCHAR2 value, even if the parameter value is a number.
If the value parameter is set to NULL or is not specified, then the parameter is set to its default value.
If you set the parallelism apply process parameter to a value greater than 1, then you must specify a conditional supplemental log group at the source database for all of the unique key and foreign key columns in the tables for which an apply process applies changes. supplemental logging might be required for other columns in these tables as well, depending on your configuration.

See Also:

"Apply Process Parameters"
The Oracle Enterprise Manager Cloud Control online help for instructions about setting an apply process parameter with Oracle Enterprise Manager Cloud Control
The DBMS_APPLY_ADM.SET_PARAMETER procedure in the Oracle Database PL/SQL Packages and Types Reference for detailed information about the apply process parameters
Oracle Streams Replication Administrator's Guide for more information about specifying supplemental logging

17.5 Setting the Apply User for an Apply Process

The apply user is the user who applies all DML changes and DDL changes that satisfy the apply process rule sets and who runs user-defined apply handlers. Set the apply user for an apply process using the apply_user parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package.

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.

For example, the following procedure sets the apply user for an apply process named strm03_apply to hr.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name => 'strm03_apply',
    apply_user => 'hr');
END;
/

Running 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, ensure that 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 custom rule-based transformation functions used in the rule set
EXECUTE privilege on all apply handler procedures

These privileges can be granted to the apply user directly or through roles.

In addition, the apply user must be granted 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.

Note:

If Oracle Database Vault is installed, then the user who changes the apply user must be granted the BECOME USER system privilege. Granting this privilege to the user is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after apply user is changed, if necessary.

17.6 Managing a DML Handler

DML handlers process row logical change records (row LCRs) dequeued by an apply process. There are two types of DML handlers: statement DML handlers and procedure DML handlers. A statement DML handler uses a collection of SQL statements to process row LCRs, while a procedure DML handler uses a PL/SQL procedure to process row LCRs.

This section contains instructions for managing a DML handler:

17.6.1 Managing a Statement DML Handler

This section contains the following instructions for managing a statement DML handler:

See Also:

17.6.1.1 Creating a Statement DML Handler and Adding It to an Apply Process

There are two ways to create a statement DML handler and add it to an apply process:

One way creates the statement DML handler, adds one statement to it, and adds the statement DML handler to an apply process all in one step.
The other way uses distinct steps to create the statement DML handler, add one or more statements to it, and add the statement DML handler to an apply process.

Typically, the one-step method is best when a statement DML handler will have only one statement. The multiple-step method is best when a statement DML handler will have several statements.

The following sections include examples that illustrate each method in detail:

17.6.1.1.1 Creating a Statement DML Handler With One Statement

In some Oracle Streams replication environments, a replicated table is not exactly the same at the databases that share the table. In such environments, a statement DML handler can modify the DML change performed by row LCRs. Statement DML handlers cannot change the values of the columns in a row LCR. However, statement DML handlers can use SQL to insert a row or update a row with column values that are different than the ones in the row LCR.

The example in this section makes the following assumptions:

An Oracle Streams replication environment is configured to replicate changes to the oe.orders table between a source database and a destination database. Changes to the oe.orders table are captured by a capture process or a synchronous capture at the source database, sent to the destination database by a propagation, and applied by an apply process at the destination database.
At the source database, the oe.orders table includes an order_status column. Assume that when an insert with an order_status of 1 is applied at the destination database, the order_status should be changed to 2. The statement DML handler in this example makes this change. For inserts with an order_status that is not equal to 1, the statement DML handler applies the original change in the row LCR without changing the order_status value.

To create a statement DML handler that modifies inserts to the oe.orders table, complete the following steps:

For the purposes of this example, specify the required supplemental logging at the source database:
1. Connect to the source database as the Oracle Streams administrator.
  
  See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
2. Specify an unconditional supplemental log group that includes the order_status column in the oe.orders table:
```
ALTER TABLE oe.orders ADD SUPPLEMENTAL LOG GROUP log_group_ord_stat  (order_status) ALWAYS;
```
  Any columns used by a statement DML handler at a destination database must be in an unconditional log group at the source database.
Connect to the destination database the Oracle Streams administrator.

Create the statement DML handler and add it to the apply process:

DECLARE
  stmt CLOB;
BEGIN
  stmt := 'INSERT INTO oe.orders(
             order_id,
             order_date, 
             order_mode,
             customer_id,
             order_status,
             order_total,
             sales_rep_id,
             promotion_id) 
           VALUES(
             :new.order_id,
             :new.order_date, 
             :new.order_mode,
             :new.customer_id,
             DECODE(:new.order_status, 1, 2, :new.order_status),
             :new.order_total,
             :new.sales_rep_id,
             :new.promotion_id)';
  DBMS_APPLY_ADM.ADD_STMT_HANDLER(
    object_name        => 'oe.orders',
    operation_name     => 'INSERT',
    handler_name       => 'modify_orders',
    statement          => stmt,
    apply_name         => 'apply$_sta_2',
    comment            => 'Modifies inserts into the orders table');
END;
/

Notice that the DECODE function changes an order_status of 1 to 2. If the order_status in the row LCR is not 1, then the DECODE function uses the original order_status value by specifying :new.order_status for the default in the DECODE function.

The ADD_STMT_HANDLER procedure creates the modify_orders statement DML handler and adds it to the apply$_sta_2 apply process. The statement DML handler is invoked when this apply process dequeues a row LCR that performs an insert on the oe.orders table. To modify row LCRs that perform updates and deletes made to this table, separate statement DML handlers are required.

Note:

This statement in the modify_orders statement DML handler performs the row change on the destination table. Therefore, you do not need to add an execute statement to the statement DML handler. The row change performed by the statement is committed when the apply process dequeues a commit directive for the row LCR's transaction.
The ADD_STMT_HANDLER procedure in this example adds the statement DML handler to the apply$_sta_2 apply process. To add a general statement DML handler that is used by all of the apply processes in the database, omit the apply_name parameter in this procedure or set the apply_name parameter to NULL.

17.6.1.1.2 Creating a Statement DML Handler With More Than One Statement

A statement DML handler can track the changes made to a table. The statement DML handler in this example tracks the updates made to the hr.jobs table.

The example in this section makes the following assumptions:

An Oracle Streams replication environment is configured to replicate changes to the hr.jobs table between a source database and a destination database. Changes to the hr.jobs table are captured by a capture process or a synchronous capture at the source database, sent to the destination database by a propagation, and applied by an apply process at the destination database. The hr.jobs table contains the minimum and maximum salary for various jobs at an organization.
The goal is to track the updates to the salary information and when these updates were made. To accomplish this goal, the statement DML handler inserts rows into the hr.track_jobs table.
The apply process must also execute the row LCRs to replicate the changes to the hr.jobs table.

To create a statement DML handler that tracks updates to the hr.jobs, complete the following steps:

Connect to the source database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Specify an unconditional supplemental log group that includes all of the columns in the hr.jobs table. For example:
```
ALTER TABLE hr.jobs ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
```
Any columns used by a statement DML handler at a destination database must be in an unconditional log group at the source database.
Connect to the destination database as the hr user.

Create a sequence for the tracking table:

CREATE SEQUENCE hr.track_jobs_seq 
   START WITH 1
   INCREMENT BY 1;

Create the table that will track the changes to the hr.jobs table:

CREATE TABLE hr.track_jobs( 
   change_id       NUMBER  CONSTRAINT track_jobs_pk PRIMARY KEY,
   job_id          VARCHAR2(10), 
   job_title       VARCHAR2(35),
   min_salary_old  NUMBER(6),
   min_salary_new  NUMBER(6),
   max_salary_old  NUMBER(6),
   max_salary_new  NUMBER(6),
   timestamp       TIMESTAMP);

The statement DML handler will use the sequence created in Step 4 to insert a unique value for each change that it tracks into the change_id column of the hr.track_jobs table.

Connect to the destination database as the Oracle Streams administrator.

Create the statement DML handler:

BEGIN
  DBMS_STREAMS_HANDLER_ADM.CREATE_STMT_HANDLER(
    handler_name => 'track_jobs',
    comment      => 'Tracks updates to the jobs table');
END;
/

Add a statement to the statement DML handler that executes the row LCR:
```
DECLARE
  stmt CLOB;
BEGIN
  stmt := ':lcr.execute TRUE';
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'track_jobs',
    statement          => stmt,
    execution_sequence => 10);
END;
/
```
The TRUE argument is for the conflict_resolution parameter in the EXECUTE member procedure for the LCR$_ROW_RECORD type. The TRUE argument indicates that any conflict resolution defined for the table is used when the row LCR is executed. Specify FALSE if you do not want conflict resolution to be used when the row LCR is executed.

Tip:

If you want to track the changes to a table without replicating them, then do not include an execute statement in the statement DML handler.

See Also:

Oracle Database PL/SQL Packages and Types Reference

Add a statement to the statement DML handler that tracks the changes the row LCR:

DECLARE
  stmt CLOB;
BEGIN
  stmt := 'INSERT INTO hr.track_jobs(
             change_id,
             job_id, 
             job_title,
             min_salary_old,
             min_salary_new,
             max_salary_old,
             max_salary_new,
             timestamp) 
           VALUES(
             hr.track_jobs_seq.NEXTVAL,
             :new.job_id,
             :new.job_title,
             :old.min_salary,
             :new.min_salary,
             :old.max_salary,
             :new.max_salary,
             :source_time)';
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'track_jobs',
    statement          => stmt,
    execution_sequence => 20);
END;
/

This statement inserts a row into the hr.track_jobs table for each row LCR that updates a row in the hr.jobs table. Notice that the values inserted into the hr.track_jobs table use the old and new values in the row LCR to track the old and new value for each salary column. Also, notice that the source_time attribute in the row LCR is used to populate the timestamp column.

Add the statement DML handler to the apply process. For example, the following procedure adds the statement DML handler to an apply process named apply$_sta_2:
```
BEGIN
  DBMS_APPLY_ADM.ADD_STMT_HANDLER(
    object_name    => 'hr.jobs',
    operation_name => 'UPDATE',
    handler_name   => 'track_jobs',
    apply_name     => 'apply$_sta_2');
END;
/
```
Note:

The ADD_STMT_HANDLER procedure in this example adds the statement DML handler to the apply$_sta_2 apply process. To add a general statement DML handler that is used by all of the apply processes in the database, omit the apply_name parameter in this procedure or set the apply_name parameter to NULL.

17.6.1.2 Adding Statements to a Statement DML Handler

To add statements to a statement DML handler, run the ADD_STMT_TO_HANDLER procedure in the DBMS_STREAMS_HANDLER_ADM package and specify an execution sequence number that has not been specified for the statement DML handler.

The example in this section adds a statement to the modify_orders statement DML handler. This statement DML handler is created in "Creating a Statement DML Handler With One Statement". It modifies inserts into the oe.orders table.

For the example in this section, assume that the destination database should discount orders by 10% for a specific customer. This customer has a customer_id value of 118 in the oe.orders table. To do this, the SQL statement in the statement DML handler multiplies the order_total value by .9 for inserts into the oe.orders table with a customer_id value of 118.

Complete the following steps to add a statement to the modify_orders statement DML handler:

Connect to the destination database where the apply process is configured as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

Check the execution sequence numbers that are already used by the statements in the statement DML handler:

COLUMN HANDLER_NAME HEADING 'Statement|Handler' FORMAT A15
COLUMN EXECUTION_SEQUENCE HEADING 'Execution|Sequence' FORMAT 999999
COLUMN STATEMENT HEADING 'Statement' FORMAT A50

SET LONG  8000
SET PAGES 8000
SELECT HANDLER_NAME,
       EXECUTION_SEQUENCE,
       STATEMENT
  FROM DBA_STREAMS_STMTS
  WHERE HANDLER_NAME = 'MODIFY_ORDERS'
  ORDER BY EXECUTION_SEQUENCE;

Your output is similar to the following:

Statement       Execution
Handler          Sequence Statement
--------------- --------- --------------------------------------------------
MODIFY_ORDERS           1 INSERT INTO oe.orders(
                                       order_id,
                                       order_date,
                                       order_mode,
                                       customer_id,
                                       order_status,
                                       order_total,
                                       sales_rep_id,
                                       promotion_id)
                                     VALUES(
                                       :new.order_id,
                                       :new.order_date,
                                       :new.order_mode,
                                       :new.customer_id,
                                       DECODE(:new.order_status, 1, 2, :new.
                          order_status),
                                       :new.order_total,
                                       :new.sales_rep_id,
                                       :new.promotion_id)

This output shows that the statement DML handler has only one statement, and this one statement is at execution sequence number 1.

Add a statement to the statement DML handler that discounts all orders by 10%:
```
DECLARE
  stmt CLOB;
BEGIN
  stmt := 'UPDATE oe.orders SET order_total=order_total*.9
             WHERE order_id=:new.order_id AND :new.customer_id=118';
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'modify_orders',
    statement          => stmt,
    execution_sequence => 10);
END;
/
```
This statement updates the row that was inserted by the statement with execution sequence number 1. Therefore, this statement must have an execution sequence number that is greater than 1. This example specifies 10 for the execution sequence number of the added statement.

Tip:

When the execution_sequence parameter is set to NULL in the ADD_STMT_TO_HANDLER procedure, the statement is added to the statement DML handler with an execution sequence number that is larger than the execution sequence number for any statement in the statement DML handler. Therefore, in this example, the execution_sequence parameter can be omitted or set to NULL.

After completing these steps, the output for the query in Step 2 shows:

Statement       Execution
Handler          Sequence Statement
--------------- --------- --------------------------------------------------
MODIFY_ORDERS           1 INSERT INTO oe.orders(
                                       order_id,
                                       order_date,
                                       order_mode,
                                       customer_id,
                                       order_status,
                                       order_total,
                                       sales_rep_id,
                                       promotion_id)
                                     VALUES(
                                       :new.order_id,
                                       :new.order_date,
                                       :new.order_mode,
                                       :new.customer_id,
                                       DECODE(:new.order_status, 1, 2, :new.
                          order_status),
                                       :new.order_total,
                                       :new.sales_rep_id,
                                       :new.promotion_id)
 
MODIFY_ORDERS          10 UPDATE oe.orders SET order_total=order_total*.9
                                       WHERE order_id=:new.order_id AND :new.
                          customer_id=118

This output shows that the new statement with execution sequence number 10 is added to the statement DML handler.

17.6.1.3 Modifying a Statement in a Statement DML Handler

To modify a statement in a statement DML handler, run the ADD_STMT_TO_HANDLER procedure in the DBMS_STREAMS_HANDLER_ADM package and specify the execution sequence number of the statement you are modifying.

The example in this section modifies the statement with execution sequence number 20 in the track_jobs statement DML handler. This statement DML handler is created in "Creating a Statement DML Handler With More Than One Statement". It uses the hr.track_jobs table to track changes to the hr.jobs table.

For the example in this section, assume that you also want to track which user updated the hr.jobs table. To do this, you must add this information to the row LCRs captured at the source database, add a user_name column to the hr.track_jobs table, and modify the statement in the statement DML handler to track the user.

Complete the following steps to modify the statement in the statement DML handler:

Connect to the source database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Add the username to the row LCR information captured at the source database:
```
BEGIN
  DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
    capture_name   => 'sta$cap',
    attribute_name => 'username',
    include        => TRUE);
END;
/
```
In the capture_name parameter, specify the capture process or synchronous capture that captures the changes that will be processed by the statement DML handler.
Connect to the destination database as the Oracle Streams administrator.

Add the user_name column to the hr.track_jobs table:

ALTER TABLE hr.track_jobs
  ADD (user_name VARCHAR2(30));

Modify the statement with execution sequence number 20 in the track_jobs statement DML handler:

DECLARE
  stmt CLOB;
BEGIN
  stmt := 'INSERT INTO hr.track_jobs(
             change_id,
             job_id, 
             job_title,
             min_salary_old,
             min_salary_new,
             max_salary_old,
             max_salary_new,
             timestamp,
             user_name) 
           VALUES(
             hr.track_jobs_seq.NEXTVAL,
             :new.job_id,
             :new.job_title,
             :old.min_salary,
             :new.min_salary,
             :old.max_salary,
             :new.max_salary,
             :source_time,
             :extra_attribute.username)';
  DBMS_STREAMS_HANDLER_ADM.ADD_STMT_TO_HANDLER(
    handler_name       => 'track_jobs',
    statement          => stmt,
    execution_sequence => 20);
END;
/

The modified statement adds user tracking by inserting the username information in the row LCR into the user_name column in the hr.track_jobs table. Notice that username is an extra LCR attribute and must be specified using the following syntax:

:extra_attribute.username

See Also:

"Extra Information in LCRs"

17.6.1.4 Removing Statements from a Statement DML Handler

To remove a statement from a statement DML handler, run the REMOVE_STMT_FROM_HANDLER procedure in the DBMS_STREAMS_HANDLER_ADM package and specify the execution sequence number of the statement you are removing.

The example in this section removes the statement with execution sequence number 10 from the track_jobs statement DML handler. This statement DML handler is created in "Creating a Statement DML Handler With More Than One Statement". It uses the hr.track_jobs table to track changes to the hr.jobs table.

For the example in this section, assume that you no longer want to execute the row LCRs with updates to the hr.jobs table. To do this, you must remove the statement that executes the row LCRs, and this statement uses execution sequence number 10 in the track_jobs statement DML handler.

Complete the following steps to remove the statement from the statement DML handler:

Connect to the database that contains the statement DML handler as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

Remove the statement from the statement DML handler:

BEGIN
  DBMS_STREAMS_HANDLER_ADM.REMOVE_STMT_FROM_HANDLER(
    handler_name       => 'track_jobs',
    execution_sequence => 10);
END;
/

17.6.1.5 Removing a Statement DML Handler from an Apply Process

To remove a statement DML handler from an apply process, run the REMOVE_STMT_HANDLER procedure in the DBMS_APPLY_ADM package.

The example in this section removes the track_jobs statement DML handler from the apply$_sta_2 apply process. This statement DML handler is created in "Creating a Statement DML Handler With More Than One Statement". It uses the hr.track_jobs table to track changes to the hr.jobs table.

Complete the following steps to remove the statement DML handler from the apply process:

Connect to the database that contains the apply process as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

Remove the statement DML handler from the apply process:

BEGIN
  DBMS_APPLY_ADM.REMOVE_STMT_HANDLER(
    object_name    => 'hr.jobs',
    operation_name => 'UPDATE',
    handler_name   => 'track_jobs',
    apply_name     => 'apply$_sta_2');
END;
/

After the statement DML handler is removed from the apply process, the statement DML handler still exists in the database.

17.6.1.6 Dropping a Statement DML Handler

To drop a statement DML handler from a database, run the DROP_STMT_HANDLER procedure in the DBMS_STREAMS_HANDLER_ADM package.

The example in this section drops the track_jobs statement DML handler. This statement DML handler is created in "Creating a Statement DML Handler With More Than One Statement". It uses the hr.track_jobs table to track changes to the hr.jobs table.

Complete the following steps to drop the statement DML handler:

Connect to the database that contains the statement DML handler as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

Drop the statement DML handler:

exec DBMS_STREAMS_HANDLER_ADM.DROP_STMT_HANDLER('track_jobs');

17.6.2 Managing a Procedure DML Handler

This section contains the following instructions for managing a procedure DML handler:

See Also:

17.6.2.1 Creating a Procedure DML Handler

A procedure DML handler 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 an ANYDATA encapsulation of a row logical change record (row LCR).

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 row 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 key 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 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).
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 key 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.

A procedure DML handler can be used for any customized processing of row LCRs. For example, the handler can modify an LCR and then execute it using the EXECUTE member procedure for the LCR. When you execute a row LCR in a procedure DML handler, the apply process applies the LCR without calling the procedure DML handler again.

You can also use SQL generation in a procedure DML handler to record the DML changes made to a table. You can record these changes in a table or in a file. For example, the sample procedure DML handler in this section uses SQL generation to record each UPDATE SQL statement made to the hr.departments table using the GET_ROW_TEXT member procedure. The procedure DML handler also applies the row LCR using the EXECUTE member procedure.

To create the procedure used in this procedure DML handler, complete the following steps:

In SQL*Plus, connect to the database as the Oracle Streams administrator.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Create the directory object for the directory that contains the text file.
In this example, the apply process writes the UPDATE SQL statements performed on the hr.departments table to the text file in this directory.

For example, to create a directory object named SQL_GEN_DIR for the /usr/sql_gen directory, enter the following SQL statement:
```
CREATE DIRECTORY SQL_GEN_DIR AS '/usr/sql_gen';
```
Ensure that the text file to which the SQL statements will be written exists in the directory specified in Step 2.

In this example, ensure that the sql_gen_file.txt file exists in the /usr/sql_gen directory on the file system.

Create the procedure for the procedure DML handler:

CREATE OR REPLACE PROCEDURE strmadmin.sql_gen_dep(lcr_anydata IN SYS.ANYDATA) IS
  lcr          SYS.LCR$_ROW_RECORD;
  int          PLS_INTEGER;
  row_txt_clob CLOB;
  fp           UTL_FILE.FILE_TYPE;
BEGIN
  int   := lcr_anydata.GETOBJECT(lcr);
  DBMS_LOB.CREATETEMPORARY(row_txt_clob, TRUE);
  -- Generate SQL from row LCR and save to file
  lcr.GET_ROW_TEXT(row_txt_clob);
  fp := UTL_FILE.FOPEN (
     location     => 'SQL_GEN_DIR',
     filename     => 'sql_gen_file.txt',
     open_mode    => 'a',
     max_linesize => 5000);
  UTL_FILE.PUT_LINE(
     file      => fp,
     buffer    => row_txt_clob,
     autoflush => TRUE);
  DBMS_LOB.TRIM(row_txt_clob, 0);
  UTL_FILE.FCLOSE(fp); 
  --  Apply row LCR
  lcr.EXECUTE(TRUE);
END;
/

After you create the procedure, you can set it as a procedure DML handler by following the instructions in "Setting a Procedure DML Handler".

Note:

You must specify an unconditional supplemental log group at the source database for any columns needed by a procedure DML handler at the destination database. This sample procedure DML handler does not require any additional supplemental logging because it records the SQL statement and does not manipulate the row LCR in any other way.
To test a procedure DML handler before using it, or to debug a procedure DML handler, you can construct row LCRs and run the procedure DML handler procedure outside the context of an apply process.

See Also:

"SQL Generation"
Oracle Database PL/SQL Packages and Types Reference for information about the GET_ROW_TEXT row LCR member function
"Supplemental Logging in an Oracle Streams Environment"
Oracle Streams Replication Administrator's Guide
"Are There Any Apply Errors in the Error Queue?" for information about common apply errors that you might want to handle in a procedure DML handler
Oracle Database SQL Language Reference for information about data types

17.6.2.2 Setting a Procedure DML Handler

A procedure DML handler processes each row LCR dequeued by any apply process that contains a specific operation on a specific table. You can specify multiple procedure DML handlers on the same table, to handle different operations on the table. All apply processes that apply changes to the specified table in the local database use the specified procedure DML handler.

Set the procedure DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the procedure DML handler for UPDATE operations on the hr.departments table. Therefore, when any apply process that applies changes locally dequeues a row LCR containing an UPDATE operation on the hr.departments table, the apply process sends the row LCR to the sql_gen_dep PL/SQL procedure in the strmadmin schema for processing. The apply process does not apply a row LCR containing such a change directly.

In this example, the apply_name parameter is set to NULL. Therefore, the procedure DML handler is a general procedure DML handler that is used by all of the apply processes in the database.

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'hr.departments',
    object_type         => 'TABLE',
    operation_name      => 'UPDATE',
    error_handler       => FALSE,
    user_procedure      => 'strmadmin.sql_gen_dep',
    apply_database_link => NULL,
    apply_name          => NULL);
END;
/

Note:

To specify the procedure DML handler for only one apply process, specify the apply process name in the apply_name parameter.
If an apply process applies changes to a remote non-Oracle database, then it can use a different procedure DML handler for the same table. You can run the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package to specify a procedure DML handler for changes that will be applied to a remote non-Oracle database by setting the apply_database_link parameter to a non-NULL value.
You can specify DEFAULT for the operation_name parameter to set the procedure as the default procedure DML handler for the database object. In this case, the procedure DML handler is used for any INSERT, UPDATE, DELETE, and LOB_WRITE on the database object, if another procedure DML handler is not specifically set for the operation on the database object.

17.6.2.3 Unsetting a Procedure DML Handler

You unset a procedure DML handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run that procedure, set the user_procedure parameter to NULL for a specific operation on a specific table. After the procedure DML handler is unset, any apply process that applies changes locally will apply a row LCR containing such a change directly.

For example, the following procedure unsets the procedure DML handler for UPDATE operations on the hr.departments table:

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name    => 'hr.departments',
    object_type    => 'TABLE',
    operation_name => 'UPDATE',
    error_handler  => FALSE,
    user_procedure => NULL,
    apply_name     => NULL);
END;
/

17.7 Managing a DDL Handler

This section contains instructions for creating, specifying, and removing the DDL handler for an apply process.

Note:

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 Also:

"Message Processing Options for an Apply Process"
Oracle Database PL/SQL Packages and Types Reference for more information about the EXECUTE member procedure for LCR types

17.7.1 Creating a DDL Handler for an Apply Process

A DDL handler 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. The parameter passed to the procedure is an ANYDATA encapsulation of a DDL LCR.

A DDL handler can be used for any customized processing of DDL LCRs. For example, the handler can modify the LCR and then execute it using the EXECUTE member procedure for the LCR. When you execute a DDL LCR in a DDL handler, the apply process applies the LCR without calling the DDL handler again.

You can also use a DDL handler to record the history of DDL changes. For example, a DDL handler can insert information about an LCR it processes into a table and then apply the LCR using the EXECUTE member procedure.

To create such a DDL handler, first create a table to hold the history information:

CREATE TABLE strmadmin.history_ddl_lcrs(
  timestamp             DATE,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  object_type           VARCHAR2(18),
  ddl_text              CLOB,
  logon_user            VARCHAR2(32),
  current_schema        VARCHAR2(32),
  base_table_owner      VARCHAR2(32),
  base_table_name       VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(10),
  scn                   NUMBER);

CREATE OR REPLACE PROCEDURE history_ddl(in_any IN ANYDATA)  
 IS
   lcr       SYS.LCR$_DDL_RECORD;
   rc        PLS_INTEGER;
   ddl_text  CLOB;
 BEGIN
   -- Access the LCR
   rc := in_any.GETOBJECT(lcr);
   DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
   lcr.GET_DDL_TEXT(ddl_text);
   --  Insert DDL LCR information into history_ddl_lcrs table
   INSERT INTO strmadmin.history_ddl_lcrs VALUES( 
     SYSDATE, lcr.GET_SOURCE_DATABASE_NAME(), lcr.GET_COMMAND_TYPE(), 
     lcr.GET_OBJECT_OWNER(), lcr.GET_OBJECT_NAME(), lcr.GET_OBJECT_TYPE(), 
     ddl_text, lcr.GET_LOGON_USER(), lcr.GET_CURRENT_SCHEMA(), 
     lcr.GET_BASE_TABLE_OWNER(), lcr.GET_BASE_TABLE_NAME(), lcr.GET_TAG(), 
     lcr.GET_TRANSACTION_ID(), lcr.GET_SCN());
   --  Apply DDL LCR
   lcr.EXECUTE();
   -- Free temporary LOB space
   DBMS_LOB.FREETEMPORARY(ddl_text);
END;
/

17.7.2 Setting the DDL Handler for an Apply Process

A DDL handler processes all DDL LCRs dequeued by an apply process. Set the DDL handler for an apply process using the ddl_handler parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the DDL handler for an apply process named strep01_apply to the history_ddl procedure in the strmadmin schema.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name  => 'strep01_apply',
    ddl_handler => 'strmadmin.history_ddl');
END;
/

17.7.3 Removing the DDL Handler for an Apply Process

A DDL handler processes all DDL LCRs dequeued by an apply process. You remove the DDL handler for an apply process by setting the remove_ddl_handler parameter to TRUE in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure removes the DDL handler from an apply process named strep01_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name         => 'strep01_apply',
    remove_ddl_handler => TRUE);
END;
/

17.8 Managing the Message Handler for an Apply Process

A message handler is an apply handler that processes persistent user messages. The following sections contain instructions for setting and unsetting the message handler for an apply process:

See Also:

"Types of Messages That Can Be Processed with an Apply Process"
"Message Handlers"
Oracle Database Advanced Queuing User's Guide for an example that creates a message handler

17.8.1 Setting the Message Handler for an Apply Process

Set the message handler for an apply process using the message_handler parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the message handler for an apply process named strm03_apply to the mes_handler procedure in the oe schema.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name      => 'strm03_apply',
    message_handler => 'oe.mes_handler');
END;
/

The user who runs the ALTER_APPLY procedure must have EXECUTE privilege on the specified message handler. If the message handler is already set for an apply process, then you can run the ALTER_APPLY procedure to change the message handler for the apply process.

17.8.2 Unsetting the Message Handler for an Apply Process

You unset the message handler for an apply process by setting the remove_message_handler parameter to TRUE in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure unsets the message handler for an apply process named strm03_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name             => 'strm03_apply',
    remove_message_handler => TRUE);
END;
/

17.9 Managing the Precommit Handler for an Apply Process

A precommit handler is an apply handler that can receive the commit information for a transaction and process the commit information in any customized way.

The following sections contain instructions for creating, setting, and unsetting the precommit handler for an apply process:

17.9.1 Creating a Precommit Handler for an Apply Process

A precommit handler 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 a commit SCN from an internal commit directive in the queue used by the apply process.

You can use a precommit handler to record information about commits processed by an apply process. The apply process can apply captured LCRs, persistent LCRs, or persistent user messages. For a captured row LCR, a commit directive contains the commit SCN of the transaction from the source database. For a persistent LCRs and persistent user messages, the commit SCN is generated by the apply process.

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 save point created in the procedure.

If a precommit handler raises an exception, then the entire apply transaction is rolled back, and all of the messages in the transaction are moved to the error queue.

For example, a precommit handler can be used for auditing the row LCRs applied by an apply process. Such a precommit handler is used with one or more separate procedure DML handlers to record the source database commit SCN for a transaction, and possibly the time when the apply process applies the transaction, in an audit table.

Specifically, this example creates a precommit handler that is used with a procedure DML handler that records information about row LCRs in the following table:

CREATE TABLE strmadmin.history_row_lcrs(
  timestamp             DATE,
  source_database_name  VARCHAR2(128),
  command_type          VARCHAR2(30),
  object_owner          VARCHAR2(32),
  object_name           VARCHAR2(32),
  tag                   RAW(10),
  transaction_id        VARCHAR2(10),
  scn                   NUMBER,
  commit_scn            NUMBER,
  old_values            SYS.LCR$_ROW_LIST,
  new_values            SYS.LCR$_ROW_LIST)
    NESTED TABLE old_values STORE AS old_values_ntab
    NESTED TABLE new_values STORE AS new_values_ntab;

The procedure DML handler inserts a row in the strmadmin.history_row_lcrs table for each row LCR processed by an apply process. The precommit handler created in this example inserts a row into the strmadmin.history_row_lcrs table when a transaction commits.

Create the procedure that inserts the commit information into the history_row_lcrs table:

CREATE OR REPLACE PROCEDURE strmadmin.history_commit(commit_number IN NUMBER)  
 IS
 BEGIN
  -- Insert commit information into the history_row_lcrs table
  INSERT INTO strmadmin.history_row_lcrs (timestamp, commit_scn) 
    VALUES (SYSDATE, commit_number);
END;
/

See Also:

17.9.2 Setting the Precommit Handler for an Apply Process

A precommit handler processes all commit directives dequeued by an apply process. When you set a precommit handler for an apply process, the apply process uses it to process all of the commit directives that it dequeues. An apply process can have only one precommit handler.

Set the precommit handler for an apply process using the precommit_handler parameter in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure sets the precommit handler for an apply process named strm01_apply to the history_commit procedure in the strmadmin schema.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name        => 'strm01_apply',
    precommit_handler => 'strmadmin.history_commit');
END;
/

You can also specify a precommit handler when you create an apply process using the CREATE_APPLY procedure in the DBMS_APPLY_ADM package. If the precommit handler is already set for an apply process, then you can run the ALTER_APPLY procedure to change the precommit handler for the apply process.

17.9.3 Unsetting the Precommit Handler for an Apply Process

You unset the precommit handler for an apply process by setting the remove_precommit_handler parameter to TRUE in the ALTER_APPLY procedure in the DBMS_APPLY_ADM package. For example, the following procedure unsets the precommit handler for an apply process named strm01_apply.

BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
    apply_name               => 'strm01_apply',
    remove_precommit_handler => TRUE);
END;
/

17.10 Specifying That Apply Processes Enqueue Messages

This section contains instructions for setting a destination queue into which apply processes that use a specified rule in a positive rule set will enqueue messages that satisfy the rule. This section also contains instructions for removing destination queue settings.

17.10.1 Setting the Destination Queue for Messages that Satisfy a Rule

You use the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package to set a destination queue for messages that satisfy a specific rule. For example, to set the destination queue for a rule named employees5 to the queue hr.change_queue, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
    rule_name               =>  'employees5',
    destination_queue_name  =>  'hr.change_queue');
END;
/

This procedure modifies the action context of the rule to specify the queue. Any apply process in the local database with the employees5 rule in its positive rule set will enqueue a message into hr.change_queue if the message satisfies the employees5 rule. To change the destination queue for the employees5 rule, run the SET_ENQUEUE_DESTINATION procedure again and specify a different queue.

The apply user of each apply process 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.

A message that has been enqueued using the SET_ENQUEUE_DESTINATION procedure is the same as any other message that is enqueued manually. Such messages can be manually dequeued, applied by an apply process created with the apply_captured parameter set to FALSE, or propagated to another queue.

Note:

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 message into the destination queue.
The apply process always enqueues messages into a persistent queue. It cannot enqueue messages into a buffered queue.

See Also:

"Enabling a User to Perform Operations on a Secure Queue"
"Enqueue Destinations for Messages During Apply" for more information about how the SET_ENQUEUE_DESTINATION procedure modifies the action context of the specified rule

17.10.2 Removing the Destination Queue Setting for a Rule

You use the SET_ENQUEUE_DESTINATION procedure in the DBMS_APPLY_ADM package to remove a destination queue for messages that satisfy a specified rule. Specifically, you set the destination_queue_name parameter in this procedure to NULL for the rule. When a destination queue specification is removed for a rule, messages that satisfy the rule are no longer enqueued into the queue by an apply process.

For example, to remove the destination queue for a rule named employees5, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
    rule_name               =>  'employees5',
    destination_queue_name  =>  NULL);
END;
/

Any apply process in the local database with the employees5 rule in its positive rule set no longer enqueues a message into hr.change_queue if the message satisfies the employees5 rule.

17.11 Specifying Execute Directives for Apply Processes

This section contains instructions for setting an apply process execute directive for messages that satisfy a specified rule in the positive rule set for the apply process.

17.11.1 Specifying that Messages that Satisfy a Rule Are Not Executed

You use the SET_EXECUTE procedure in the DBMS_APPLY_ADM package to specify that apply processes do not execute messages that satisfy a specified rule. Specifically, you set the execute parameter in this procedure to FALSE for the rule. After setting the execution directive to FALSE for a rule, an apply process with the rule in its positive rule set does not execute a message that satisfies the rule.

For example, to specify that apply processes do not execute messages that satisfy a rule named departments8, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_EXECUTE(
    rule_name   =>  'departments8',
    execute     =>  FALSE);
END;
/

This procedure modifies the action context of the rule to specify the execution directive. Any apply process in the local database with the departments8 rule in its positive rule set will not execute a message if the message satisfies the departments8 rule. That is, if the message is an LCR, then an apply process does not apply the change in the LCR to the relevant database object. Also, an apply process does not send a message that satisfies this rule to any apply handler.

Note:

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.
The SET_EXECUTE procedure can be used with the SET_ENQUEUE_DESTINATION procedure to enqueue messages that satisfy a particular rule into a destination queue without executing these messages. After a message is enqueued using the SET_ENQUEUE_DESTINATION procedure, it is the same as any message that is enqueued manually. Therefore, it can be manually dequeued, applied by an apply process, or propagated to another queue.

See Also:

"Execution Directives for Messages During Apply" for more information about how the SET_EXECUTE procedure modifies the action context of the specified rule
"Specifying That Apply Processes Enqueue Messages"

17.11.2 Specifying that Messages that Satisfy a Rule Are Executed

You use the SET_EXECUTE procedure in the DBMS_APPLY_ADM package to specify that apply processes execute messages that satisfy a specified rule. Specifically, you set the execute parameter in this procedure to TRUE for the rule. By default, each apply process executes messages that satisfy a rule in the positive rule set for the apply process, assuming that the message does not satisfy a rule in the negative rule set for the apply process. Therefore, you must set the execute parameter to TRUE for a rule only if this parameter was set to FALSE for the rule earlier.

For example, to specify that apply processes executes messages that satisfy a rule named departments8, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_EXECUTE(
    rule_name   =>  'departments8',
    execute     =>  TRUE);
END;
/

Any apply process in the local database with the departments8 rule in its positive rule set will execute a message if the message satisfies the departments8 rule. That is, if the message is an LCR, then an apply process applies the change in the LCR to the relevant database object. Also, an apply process sends a message that satisfies this rule to an apply handler if it is configured to do so.

17.12 Managing an Error Handler

An error handler handles errors resulting from a row LCR dequeued by any apply process that contains a specific operation on a specific table.

The following sections contain instructions for creating, setting, and unsetting an error handler:

See Also:

17.12.1 Creating an Error Handler

You create an error handler by running the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package and setting the error_handler parameter to TRUE.

An error handler 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);

Here, user_procedure stands for the name of the procedure. Each parameter is required and must have the specified data type. However, you can change the names of the parameters. The emsg_array parameter must be a user-defined array that is a PL/SQL table of type VARCHAR2 with at least 76 characters.

Note:

Some conditions on the user procedure specified in SET_DML_HANDLER must be met for error handlers. See "Managing a DML Handler" for information about these conditions.

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

The error handler successfully resolves the error, applies the row LCR if appropriate, and returns control back 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.

If you want to retry the DML operation, then have the error handler procedure run the EXECUTE member procedure for the LCR.

The following example creates an error handler named regions_pk_error that resolves primary key violations for the hr.regions table. At a destination database, assume users insert rows into the hr.regions table and an apply process applies changes to the hr.regions table that originated from a capture process at a remote source database. In this environment, there is a possibility of errors resulting from users at the destination database inserting a row with the same primary key value as an insert row LCR applied from the source database.

This example creates a table in the strmadmin schema called errorlog to record the following information about each primary key violation error on the hr.regions table:

The time stamp when the error occurred
The name of the apply process that raised the error
The user who caused the error (sender), which is the capture process name for captured LCRs, the synchronous capture name for persistent LCRs captured by the synchronous capture, or the name of the Oracle Database Advanced Queuing (AQ) agent for persistent LCRs and persistent user messages enqueued by an application
The name of the object on which the DML operation was run, because errors for other objects might be logged in the future
The type of command used in the DML operation
The name of the constraint violated
The error message
The LCR that caused the error

This error handler resolves only errors that are caused by a primary key violation on the hr.regions table. To resolve this type of error, the error handler modifies the region_id value in the row LCR using a sequence and then executes the row LCR to apply it. If other types of errors occur, then you can use the row LCR you stored in the errorlog table to resolve the error manually.

For example, the following error is resolved by the error handler:

At the destination database, a user inserts a row into the hr.regions table with a region_id value of 6 and a region_name value of 'LILLIPUT'.
At the source database, a user inserts a row into the hr.regions table with a region_id value of 6 and a region_name value of 'BROBDINGNAG'.
A capture process at the source database captures the change described in Step 2.
A propagation propagates the LCR containing the change from a queue at the source database to the queue used by the apply process at the destination database.
When the apply process tries to apply the LCR, an error results because of a primary key violation.
The apply process invokes the error handler to handle the error.
The error handler logs the error in the strmadmin.errorlog table.
The error handler modifies the region_id value in the LCR using a sequence and executes the LCR to apply it.

Complete the following steps to create the regions_pk_error error handler:

In SQL*Plus, connect to the database as the hr user.

See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.
Create the sequence used by the error handler to assign new primary key values:
```
CREATE SEQUENCE hr.reg_exception_s START WITH 9000;
```
This example assumes that users at the destination database will never insert a row into the hr.regions table with a region_id greater than 8999.
Grant the Oracle Streams administrator ALL privilege on the sequence:
```
GRANT ALL ON reg_exception_s TO strmadmin;
```
Connect to the database as the Oracle Streams administrator.

Create the errorlog table:

CREATE TABLE strmadmin.errorlog(
  logdate       DATE,
  apply_name    VARCHAR2(30),
  sender        VARCHAR2(100),
  object_name   VARCHAR2(32),
  command_type  VARCHAR2(30),
  errnum        NUMBER,
  errmsg        VARCHAR2(2000),
  text          VARCHAR2(2000),
  lcr           SYS.LCR$_ROW_RECORD);

Create a package that includes the regions_pk_error procedure:

CREATE OR REPLACE PACKAGE errors_pkg 
AS
 TYPE emsg_array IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
 PROCEDURE regions_pk_error( 
   message            IN ANYDATA,
   error_stack_depth  IN NUMBER,
   error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY,
   error_messages     IN EMSG_ARRAY);
END errors_pkg ;
/

Create the package body:

CREATE OR REPLACE PACKAGE BODY errors_pkg AS
 PROCEDURE regions_pk_error ( 
   message            IN ANYDATA,
   error_stack_depth  IN NUMBER,
   error_numbers      IN DBMS_UTILITY.NUMBER_ARRAY,
   error_messages     IN EMSG_ARRAY )
 IS
  reg_id     NUMBER;
  ad         ANYDATA;
  lcr        SYS.LCR$_ROW_RECORD;
  ret        PLS_INTEGER;
  vc         VARCHAR2(30);
  apply_name VARCHAR2(30);
  errlog_rec errorlog%ROWTYPE ;
  ov2        SYS.LCR$_ROW_LIST;
 BEGIN
  -- Access the error number from the top of the stack.
  -- In case of check constraint violation,
  -- get the name of the constraint violated.
  IF error_numbers(1) IN ( 1 , 2290 ) THEN
   ad  := DBMS_STREAMS.GET_INFORMATION('CONSTRAINT_NAME');
   ret := ad.GetVarchar2(errlog_rec.text);
  ELSE 
   errlog_rec.text := NULL ;
  END IF ;
  -- Get the name of the sender and the name of the apply process.
  ad  := DBMS_STREAMS.GET_INFORMATION('SENDER');
  ret := ad.GETVARCHAR2(errlog_rec.sender);
  apply_name := DBMS_STREAMS.GET_STREAMS_NAME();
  -- Try to access the LCR.
  ret := message.GETOBJECT(lcr);
  errlog_rec.object_name  := lcr.GET_OBJECT_NAME() ;
  errlog_rec.command_type := lcr.GET_COMMAND_TYPE() ;
  errlog_rec.errnum := error_numbers(1) ;
  errlog_rec.errmsg := error_messages(1) ;
  INSERT INTO strmadmin.errorlog VALUES (SYSDATE, apply_name, 
       errlog_rec.sender, errlog_rec.object_name, errlog_rec.command_type, 
       errlog_rec.errnum, errlog_rec.errmsg, errlog_rec.text, lcr);
  -- Add the logic to change the contents of LCR with correct values.
  -- In this example, get a new region_id number 
  -- from the hr.reg_exception_s sequence.
  ov2 := lcr.GET_VALUES('new', 'n');
  FOR i IN 1 .. ov2.count
  LOOP
    IF ov2(i).column_name = 'REGION_ID' THEN
     SELECT hr.reg_exception_s.NEXTVAL INTO reg_id FROM DUAL; 
     ov2(i).data := ANYDATA.ConvertNumber(reg_id) ;
    END IF ;
  END LOOP ;
  -- Set the NEW values in the LCR.
  lcr.SET_VALUES(value_type => 'NEW', value_list => ov2);
  -- Execute the modified LCR to apply it.
  lcr.EXECUTE(TRUE);
 END regions_pk_error;
END errors_pkg;
/

Note:

For subsequent changes to the modified row to be applied successfully, you should converge the rows at the two databases as quickly as possible. That is, you should make the region_id for the row match at the source and destination database. If you do not want these manual changes to be recaptured at a database, then use the SET_TAG procedure in the DBMS_STREAMS package to set the tag for the session in which you make the change to a value that is not captured.
This example error handler illustrates the use of the GET_VALUES member function and SET_VALUES member procedure for the LCR. If you are modifying only one value in the LCR, then the GET_VALUE member function and SET_VALUE member procedure might be more convenient and more efficient.

See Also:

Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session
"Are There Any Apply Errors in the Error Queue?" for information about specific error messages to handle in an error handler

17.12.2 Setting an Error Handler

An error handler handles errors resulting from a row LCR dequeued by any apply process that contains a specific operation on a specific table. You can specify multiple error handlers on the same table, to handle errors resulting from different operations on the table. You can either set an error handler for a specific apply process, or you can set an error handler as a general error handler that is used by all apply processes that apply the specified operation to the specified table.

Set an error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run this procedure to set an error handler, set the error_handler parameter to TRUE.

For example, the following procedure sets the error handler for INSERT operations on the hr.regions table. Therefore, when any apply process dequeues a row LCR containing an INSERT operation on the local hr.regions table, and the row LCR results in an error, the apply process sends the row LCR to the strmadmin.errors_pkg.regions_pk_error PL/SQL procedure for processing. If the error handler cannot resolve the error, then the row LCR and all of the other row LCRs in the same transaction are moved to the error queue.

In this example, the apply_name parameter is set to NULL. Therefore, the error handler is a general error handler that is used by all of the apply processes in the database.

Run the following procedure to set the error handler:

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name         => 'hr.regions',
    object_type         => 'TABLE',
    operation_name      => 'INSERT',
    error_handler       => TRUE,
    user_procedure      => 'strmadmin.errors_pkg.regions_pk_error',
    apply_database_link => NULL,
    apply_name          => NULL);
END;
/

If the error handler is already set, then you can run the SET_DML_HANDLER procedure to change the error handler.

17.12.3 Unsetting an Error Handler

You unset an error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package. When you run that procedure, set the user_procedure parameter to NULL for a specific operation on a specific table.

For example, the following procedure unsets the error handler for INSERT operations on the hr.regions table:

BEGIN
  DBMS_APPLY_ADM.SET_DML_HANDLER(
    object_name    => 'hr.regions',
    object_type    => 'TABLE',
    operation_name => 'INSERT',
    user_procedure => NULL,
    apply_name     => NULL);
END;
/

Note:

The error_handler parameter does not need to be specified.

17.13 Managing Apply Errors

The following sections contain instructions for retrying and deleting apply errors:

See Also:

"The Error Queue"
"Checking for Apply Errors"
"Displaying Detailed Information About Apply Errors"
The Oracle Enterprise Manager Cloud Control online help for information about managing apply errors in Oracle Enterprise Manager Cloud Control
"Considerations for Applying DML Changes to Tables" for information about the possible causes of apply errors

17.13.1 Retrying Apply Error Transactions

You can retry a specific error transaction or you can retry all error transactions for an apply process. You might need to make DML or DDL changes to database objects to correct the conditions that caused one or more apply errors before you retry error transactions. You can also have one or more capture processes or synchronous captures configured to capture changes to the same database objects, but you might not want the changes captured. In this case, you can set the session tag to a value that will not be captured for the session that makes the changes.

The following topics provide more information about retrying apply error transactions:

See Also:

Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current session

17.13.1.1 Retrying a Specific Apply Error Transaction

When you retry an error transaction, you can execute it immediately or send the error transaction to a user procedure for modifications before executing it. The following sections provide instructions for each method:

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the EXECUTE_ERROR procedure

17.13.1.1.1 Retrying a Specific Apply Error Transaction Without a User Procedure

After you correct the conditions that caused an apply error, you can retry the transaction by running the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package without specifying a user procedure. In this case, the transaction is executed without any custom processing.

For example, to retry a transaction with the transaction identifier 5.4.312, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.EXECUTE_ERROR(
    local_transaction_id => '5.4.312',
    execute_as_user      => FALSE,
    user_procedure       => NULL);
END;
/

If execute_as_user is TRUE, then the apply process executes the transaction in the security context of the current user. If execute_as_user is FALSE, then the apply process executes 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.

In either case, 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.

17.13.1.1.2 Retrying a Specific Apply Error Transaction with a User Procedure

You can retry an error transaction by running the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package, and specify a user procedure to modify one or more messages in the transaction before the transaction is executed. The modifications should enable successful execution of the transaction. The messages in the transaction can be LCRs or user messages.

For example, consider a case in which an apply error resulted because of a conflict. Examination of the error transaction reveals that the old value for the salary column in a row LCR contained the wrong value. Specifically, the current value of the salary of the employee with employee_id of 197 in the hr.employees table did not match the old value of the salary for this employee in the row LCR. Assume that the current value for this employee is 3250 in the hr.employees table.

Given this scenario, the following user procedure modifies the salary in the row LCR that caused the error:

CREATE OR REPLACE PROCEDURE strmadmin.modify_emp_salary(
  in_any                        IN      ANYDATA,
  error_record                  IN      DBA_APPLY_ERROR%ROWTYPE,
  error_message_number          IN      NUMBER,
  messaging_default_processing  IN OUT  BOOLEAN,
  out_any                       OUT     ANYDATA)
AS
  row_lcr          SYS.LCR$_ROW_RECORD;
  row_lcr_changed  BOOLEAN := FALSE;
  res              NUMBER;
  ob_owner         VARCHAR2(32);
  ob_name          VARCHAR2(32);
  cmd_type         VARCHAR2(30);
  employee_id      NUMBER;
BEGIN
  IF in_any.getTypeName() = 'SYS.LCR$_ROW_RECORD' THEN
    -- Access the LCR
    res := in_any.GETOBJECT(row_lcr);
    -- Determine the owner of the database object for the LCR
    ob_owner := row_lcr.GET_OBJECT_OWNER;
    -- Determine the name of the database object for the LCR
    ob_name := row_lcr.GET_OBJECT_NAME;
    -- Determine the type of DML change
    cmd_type := row_lcr.GET_COMMAND_TYPE;
    IF (ob_owner = 'HR' AND ob_name = 'EMPLOYEES' AND cmd_type = 'UPDATE') THEN
      -- Determine the employee_id of the row change
      IF row_lcr.GET_VALUE('old', 'employee_id') IS NOT NULL THEN
        employee_id := row_lcr.GET_VALUE('old', 'employee_id').ACCESSNUMBER();
        IF (employee_id = 197) THEN
          -- error_record.message_number should equal error_message_number
          row_lcr.SET_VALUE(
          value_type => 'OLD',
          column_name => 'salary',
          column_value => ANYDATA.ConvertNumber(3250));
          row_lcr_changed := TRUE;
        END IF;
      END IF;
    END IF;
  END IF;
  -- Specify that the apply process continues to process the current message
  messaging_default_processing := TRUE;
  -- assign out_any appropriately
  IF row_lcr_changed THEN
    out_any := ANYDATA.ConvertObject(row_lcr);
  ELSE
    out_any := in_any;
  END IF;
END;
/

To retry a transaction with the transaction identifier 5.6.924 and process the transaction with the modify_emp_salary procedure in the strmadmin schema before execution, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.EXECUTE_ERROR(
    local_transaction_id => '5.6.924',
    execute_as_user      => FALSE,
    user_procedure       => 'strmadmin.modify_emp_salary');
END;
/

Note:

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

17.13.1.2 Retrying All Error Transactions for an Apply Process

After you correct the conditions that caused all of the apply errors for an apply process, you can retry all of the error transactions by running the EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package. For example, to retry all of the error transactions for an apply process named strm01_apply, you can run the following procedure:

BEGIN
  DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(
    apply_name       => 'strm01_apply',
    execute_as_user  => FALSE);
END;
/

Note:

If you specify NULL for the apply_name parameter, and you have multiple apply processes, then all of the apply errors are retried for all of the apply processes.

17.13.2 Deleting Apply Error Transactions

You can delete a specific error transaction or you can delete all error transactions for an apply process.

17.13.2.1 Deleting a Specific Apply Error Transaction

If an error transaction should not be applied, then you can delete the transaction from the error queue using the DELETE_ERROR procedure in the DBMS_APPLY_ADM package. For example, to delete a transaction with the transaction identifier 5.4.312, run the following procedure:

EXEC DBMS_APPLY_ADM.DELETE_ERROR(local_transaction_id => '5.4.312');

17.13.2.2 Deleting All Error Transactions for an Apply Process

If none of the error transactions should be applied, then you can delete all of the error transactions by running the DELETE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package. For example, to delete all of the error transactions for an apply process named strm01_apply, you can run the following procedure:

EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'strm01_apply');

Note:

If you specify NULL for the apply_name parameter, and you have multiple apply processes, then all of the apply errors are deleted for all of the apply processes.

17.14 Managing the Substitute Key Columns for a Table

The following topics contain instructions for setting and removing the substitute key columns for a table.

See Also:

17.14.1 Setting Substitute Key Columns for a Table

When an apply process applies changes to a table, substitute key columns can either replace the primary key columns for a table that has a primary key or act as the primary key columns for a table that does not have a primary key. Set the substitute key columns for a table using the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. This setting applies to all of the apply processes that apply local changes to the database.

For example, to set the substitute key columns for the hr.employees table to the first_name, last_name, and hire_date columns, replacing the employee_id column, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_KEY_COLUMNS(
    object_name         => 'hr.employees',
    column_list         => 'first_name,last_name,hire_date');
END;
/

Note:

You must specify an unconditional supplemental log group at the source database for all of the columns specified as substitute key columns in the column_list or column_table parameter at the destination database. In this example, you would specify an unconditional supplemental log group including the first_name, last_name, and hire_date columns in the hr.employees table.
If an apply process applies changes to a remote non-Oracle database, then it can use different substitute key columns for the same table. You can run the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package to specify substitute key columns for changes that will be applied to a remote non-Oracle database by setting the apply_database_link parameter to a non-NULL value.

See Also:

17.14.2 Removing the Substitute Key Columns for a Table

You remove the substitute key columns for a table by specifying NULL for the column_list or column_table parameter in the SET_KEY_COLUMNS procedure in the DBMS_APPLY_ADM package. If the table has a primary key, then the table's primary key is used by any apply process for local changes to the database after you remove the substitute primary key.

For example, to remove the substitute key columns for the hr.employees table, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_KEY_COLUMNS(
    object_name  => 'hr.employees',
    column_list  => NULL);
END;
/

17.15 Using Virtual Dependency Definitions

A virtual dependency definition is a description of a dependency that is used by an apply process to detect dependencies between transactions being applied at a destination database. Virtual dependency definitions are useful when apply process parallelism is greater than 1 and dependencies are not described by constraints in the data dictionary at the destination database. There are two types of virtual dependency definitions: value dependencies and object dependencies.

A value dependency defines a table constraint, such as a unique key, or a relationship between the columns of two or more tables. An object dependency defines a parent-child relationship between two objects at a destination database.

The following sections describe using virtual dependency definitions:

See Also:

"Apply Processes and Dependencies" for more information about virtual dependency definitions

17.15.1 Setting and Unsetting Value Dependencies

Use the SET_VALUE_DEPENDENCY procedure in the DBMS_APPLY_ADM package to set or unset a value dependency. The following sections describe scenarios for using value dependencies:

17.15.1.1 Schema Differences and Value Dependencies

This scenario involves an environment that shares many tables between a source database and destination database, but the schema that owns the tables is different at these two databases. Also, in this replication environment, the source database is in the United States and the destination database is in England. A design firm uses dozens of tables to describe product designs, but the tables use United States measurements (inches, feet, and so on) in the source database and metric measurements in the destination database. The name of the schema that owns the database objects at the source database is us_designs, while the name of the schema at the destination database is uk_designs. Therefore, the schema name of the shared database objects must be changed before apply, and all of the measurements must be converted from United States measurements to metric measurements. Both databases use the same constraints to enforce dependencies between database objects.

Rule-based transformations could make the required changes, but the goal is to apply multiple LCRs in parallel. Rule-based transformations must apply LCRs serially. So, a procedure DML handler is configured at the destination database to make the required changes to the LCRs, and apply process parallelism is set to 5. In this environment, the destination database has no information about the schema us_designs in the LCRs being sent from the source database. Because an apply process calculates dependencies before passing LCRs to apply handlers, the apply process must be informed about the dependencies between LCRs. Value dependencies can describe these dependencies.

In this scenario, suppose several tables describe different designs, and each of these tables has a primary key. One of these tables is design_53, and the primary key column is key_53. Also, a table named all_designs_summary includes a summary of all of the individual designs, and this table has a foreign key column for each design table. The all_designs_summary includes a key_53 column, which is a foreign key of the primary key in the design_53 table. To inform an apply process about the relationship between these tables, run the following procedures to create a value dependency at the destination database:

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'key_53_foreign_key',
    object_name       => 'us_designs.design_53',
    attribute_list    => 'key_53');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'key_53_foreign_key',
    object_name       => 'us_designs.all_designs_summary',
    attribute_list    => 'key_53');
END;
/

Notice that the value dependencies use the schema at the source database (us_designs) because LCRs contain the source database schema. The schema will be changed to uk_designs by the procedure DML handler after the apply process passes the row LCRs to the handler.

To unset a value dependency, run the SET_VALUE_DEPENDENCY procedure, and specify the name of the value dependency in the dependency_name parameter and NULL in the object_name parameter. For example, to unset the key_53_foreign_key value dependency that was set previously, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'key_53_foreign_key',
    object_name       => NULL,
    attribute_list    => NULL);
END;
/

See Also:

"Managing a DML Handler"

17.15.1.2 Undefined Constraints at the Destination Database and Value Dependencies

This scenarios involves an environment in which foreign key constraints are used for shared tables at the source database, but no constraints are used for these tables at the destination database. In the replication environment, the destination database is used as a data warehouse where data is written to the database far more often than it is queried. To optimize write operations, no constraints are defined at the destination database.

In such an environment, an apply processes running on the destination database must be informed about the constraints to apply transactions consistently. Value dependencies can inform the apply process about these constraints.

For example, assume that the orders and order_items tables in the oe schema are shared between the source database and the destination database in this environment. On the source database, the order_id column is a primary key in the orders table, and the order_id column in the order_items table is a foreign key that matches the primary key column in the orders table. At the destination database, these constraints have been removed. Run the following procedures to create a value dependency at the destination database that informs apply processes about the relationship between the columns in these tables:

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'order_id_foreign_key',
    object_name       => 'oe.orders',
    attribute_list    => 'order_id');
END;
/

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'order_id_foreign_key',
    object_name       => 'oe.order_items',
    attribute_list    => 'order_id');
END;
/

Also, in this environment, the following actions should be performed so that apply processes can apply transactions consistently:

Value dependencies should be set for each column that has a unique key or bitmap index at the source database.
The DBMS_APPLY_ADM.SET_KEY_COLUMNS procedure should set substitute key columns for the columns that are primary key columns at the source database.

To unset the value dependency that was set previously, run the following procedure:

BEGIN
  DBMS_APPLY_ADM.SET_VALUE_DEPENDENCY(
    dependency_name   => 'order_id_foreign_key',
    object_name       => NULL,
    attribute_list    => NULL);
END;
/

17.15.2 Creating and Dropping Object Dependencies

Use the CREATE_OBJECT_DEPENDENCY and DROP_OBJECT_DEPENDENCY procedures in the DBMS_APPLY_ADM package to create or drop an object dependency.

The following topics provide detailed instructions for creating and dropping object dependencies:

17.15.2.1 Creating an Object Dependency

An object dependency can be used when row LCRs for a particular table always should be applied before the row LCRs for another table, and the data dictionary of the destination database does not contain a constraint to enforce this relationship. When you define an object dependency, the table whose row LCRs should be applied first is the parent table and the table whose row LCRs should be applied second is the child table.

For example, consider an Oracle Streams replication environment with the following characteristics:

The following tables in the ord schema are shared between a source and destination database:
- The customers table contains information about customers, including each customer's shipping address.
- The orders table contains information about each order.
- The order_items table contains information about the items ordered in each order.
- The ship_orders table contains information about orders that are ready to ship, but it does not contain detailed information about the customer or information about individual items to ship with each order.
The ship_orders table has no relationships, defined by constraints, with the other tables.
Information about orders is entered into the source database and propagated to the destination database, where it is applied.
The destination database site is a warehouse where orders are shipped to customers. At this site, a procedure DML handler uses the information in the ship_orders, customers, orders, and order_items tables to generate a report that includes the customer's shipping address and the items to ship.

The information in the report generated by the procedure DML handler must be consistent with the time when the ship order record was created. An object dependency at the destination database can accomplish this goal. In this case, the ship_orders table is the parent table of the following child tables: customers, orders, and order_items. Because ship_orders is the parent of these tables, any changes to these tables made after a record in the ship_orders table was entered will not be applied until the procedure DML handler has generated the report for the ship order.

To create these object dependencies, run the following procedures at the destination database:

BEGIN
  DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
    object_name         =>  'ord.customers',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
    object_name         =>  'ord.orders',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.CREATE_OBJECT_DEPENDENCY(
    object_name         =>  'ord.order_items',
    parent_object_name  =>  'ord.ship_orders');
END;
/

See Also:

"Managing a DML Handler"

17.15.2.2 Dropping an Object Dependency

To drop the object dependencies created in "Creating an Object Dependency", run the following procedure:

BEGIN
  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
    object_name         =>  'ord.customers',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
    object_name         =>  'ord.orders',
    parent_object_name  =>  'ord.ship_orders');
END;
/

BEGIN
  DBMS_APPLY_ADM.DROP_OBJECT_DEPENDENCY(
    object_name         =>  'ord.order_items',
    parent_object_name  =>  'ord.ship_orders');
END;
/

17.16 Dropping an Apply Process

You run the DROP_APPLY procedure in the DBMS_APPLY_ADM package to drop an existing apply process. For example, the following procedure drops an apply process named strm02_apply:

BEGIN
  DBMS_APPLY_ADM.DROP_APPLY(
    apply_name            => 'strm02_apply',
    drop_unused_rule_sets => TRUE);
END;
/

Because the drop_unused_rule_sets parameter is set to TRUE, this procedure also drops any rule sets used by the strm02_apply apply process, unless a rule set is used by another Oracle Streams client. If the drop_unused_rule_sets parameter is set to TRUE, then both the positive and negative rule set for the apply process might be dropped. If this procedure drops a rule set, then it also drops any rules in the rule set that are not in another rule set.

An error is raised if you try to drop an apply process and there are errors in the error queue for the specified apply process. Therefore, if there are errors in the error queue for an apply process, delete the errors before dropping the apply process.

See Also:

"Managing Apply Errors"