Download Oracle 11g Streams Implementer`s Guide

Get Fancy with Streams Advanced Configurations
The ADD_SUBSET_OUTBOUND_RULES condition parameter equates to the
ADD_SUBSET_RULES dml_condition parameter. The ADD_SUBSET_OUTBOUND_RULES
keep parameter equates the ADD_SUBSET include_rule parameter (If TRUE, include
the LCRs that meet the condition. If FALSE, ignore the LCRs that meet the condition).
DECLARE
col_list DBMS_UTILITY.LNAME_ARRAY;
x number := 0;
BEGIN
select column_name from dba_tab_cols where owner = 'HR'
and table_name = 'EMPLOYEES';
for arec in (select column_name from dba_tab_cols
where owner = 'HR' and table_name = 'EMPLOYEES')
loop
x := x+1;
col_list(x) := arec.column_name;
end loop;
--last position in the array must be set to NULL
If x >0 then
x := x+1;
col_list(x) := NULL;
end if;
DBMS_XSTREAM_ADM.ADD_SUBSET_OUTBOUND_RULES(
server_name => 'HROE_Out',
table_name => 'HR.EMPLOYEES',
condition
=> 'department_id = 50',
column_list => col_list,
keep
=> TRUE);
END;
/
To remove a Subsetting rule from an Outbound Server, first determine the Subset
Rule names:
SELECT STREAMS_NAME, STREAMS_TYPE, STREAMS_RULE_TYPE,
RULE_OWNER, SUBSETTING_OPERATION, RULE_NAME
FROM DBA_XSTREAM_RULES
WHERE SUBSETTING_OPERATION IS NOT NULL;
Then use the DBMS_XSTREAM_ADM.REMOVE_SUBSET_OUTBOUND_RULES procedure to
remove the rules
DBMS_XSTREAM_ADM.REMOVE_SUBSET_OUTBOUND_RULES(
server_name
IN VARCHAR2,
insert_rule_name IN VARCHAR2,
update_rule_name IN VARCHAR2,
delete_rule_name IN VARCHAR2);
[ 244 ]