| Oracle® Streams Concepts and Administration 11g Release 2 (11.2) Part Number E17069-07 |
|
|
PDF · Mobi · ePub |
| Oracle® Streams Concepts and Administration 11g Release 2 (11.2) Part Number E17069-07 |
|
|
PDF · Mobi · ePub |
An apply process implicitly consumes information in an Oracle Streams environment. An apply process dequeues logical change records (LCRs) and user messages from a specific queue and either applies each one directly or passes it as a parameter to a user-defined procedure.
The following topics describe managing Oracle Streams apply processes:
Each task described in this chapter should be completed by an Oracle Streams administrator that has been granted the appropriate privileges, unless specified otherwise.
See Also:
Oracle Streams Replication Administrator's Guide for information about creating an Oracle Streams administrator
Oracle Database 2 Day + Data Replication and Integration Guide and the Enterprise Manager online Help for instructions on managing an apply process with Enterprise Manager
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:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions about starting an apply process with Oracle Enterprise ManagerYou 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:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions about stopping an apply process with Oracle Enterprise ManagerThis section contains instructions for completing the following tasks:
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.
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;
/
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;
/
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"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.
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.
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.
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.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:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions about setting an apply process parameter with Oracle Enterprise Manager
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
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, follow the steps outlined in "Oracle Streams and Oracle Data Vault" to ensure the correct privileges and roles have been granted.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:
This section contains the following instructions for managing a statement DML handler:
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:
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:
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 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.
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.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:
TheADD_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.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 theexecution_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.
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"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;
/
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.
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');
This section contains the following instructions for managing 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:
Oracle Database PL/SQL Packages and Types Reference for information about the GET_ROW_TEXT row LCR member function
"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
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.
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;
/
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 theEXECUTE member procedure of a DDL LCR, then a commit is performed automatically.See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the EXECUTE member procedure for LCR types
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; /
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;
/
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;
/
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"
Oracle Streams Advanced Queuing User's Guide for an example that creates a message handler
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.
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;
/
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:
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:
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.
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;
/
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.
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:
"Enqueue Destinations for Messages During Apply" for more information about how the SET_ENQUEUE_DESTINATION procedure modifies the action context of the specified 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.
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.
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
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.
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:
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 inSET_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 Streams 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 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
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.
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:
Theerror_handler parameter does not need to be specified.The following sections contain instructions for retrying and deleting apply errors:
See Also:
Oracle Database 2 Day + Data Replication and Integration Guide for instructions on managing apply errors in Oracle Enterprise Manager
"Considerations for Applying DML Changes to Tables" for information about the possible causes of apply errors
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.
See Also:
Oracle Streams Replication Administrator's Guide for more information about setting tag values generated by the current sessionWhen 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:
Retrying a Specific Apply Error Transaction Without a User Procedure
Retrying a Specific Apply Error Transaction with a User Procedure
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theEXECUTE_ERROR procedureAfter 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.
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 haveSELECT privilege on DBA_APPLY_ERROR data dictionary view.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 specifyNULL 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.You can delete a specific error transaction or you can delete all error transactions for an apply process.
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');
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 specifyNULL 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.This section contains instructions for setting and removing the substitute key columns for a table.
See Also:
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.
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;
/
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 definitionsUse 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:
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"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;
/
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 sections provide detailed instructions for creating and dropping object dependencies.
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"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;
/
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:
|
 Copyright © 2002, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|