Download Oracle 11g Streams Implementer`s Guide

Chapter 6
Propagation at the Hub
Now we want to set up propagation from the Hub to each spoke. It is here where we
evaluate the tag using the AND_CONDITION. As we want to send all changes, expect
those originating from the destination spoke, it is easier to create a negative rule.
Because propagation is enabled when it is created, we will disable it immediately
after creation. As mentioned earlier, the AND_CONDITION is a string that is appended
to system conditions created by the rule. Make sure to escape single quotes using
PL/SQL syntax. Run the following at the hub for each spoke site, substituting S1
with S2 and S3 respectively, and A1 with A2 and A3 respectively.
The following code creates the Propagation processes at the Hub. This code can be
found in the TAG_HUBProp.sql code file.
BEGIN
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'OE',
streams_name => 'OE_PROP_S1',
source_queue_name => 'S1_CAPT_Q',
destination_queue_name => '[email protected]',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => TRUE, --check null and non-null tags
source_database => 'H1.oracle.com',
inclusion_rule => FALSE, --if condition is true, don't send
and_condition => ':lcr.get_tag = HEXTORAW(''A1'')',
queue_to_queue => TRUE);
--disable propagation until we are finished
BEGIN
dbms_aqadm.disable_propagation_schedule(
queue_name => 'S1_CAPT_Q',
destination => ' S1.oracle.com ',
destination_queue => '[email protected]'
);
EXCEPTION WHEN OTHERS THEN
IF sqlcode = -24065 THEN NULL; -- propagation already disabled
ELSE RAISE;
END IF;
END;
END;
/
[ 199 ]