Filter data in Oracle Streams
- From: "Ravi Gaur" <ravigaur1@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 27 Jul 2007 10:22:15 -0500
I've a question for streams folks that have used data filtering during
capture. We are using a downstream configuration and the logs from the
source (living on a different host) are shipped and registered to the
downstream db and changes captured.
I'm trying to apply a rule to filter data based on a column value.
Specifically, this is the condition I want to apply in capture rules --
substr(SSBSECT_TERM_CODE,6,1) in ('2','6','9')
I'm using "DBMS_STREAMS_ADM.ADD_SUBSET_RULES" (per Oracle Support's
suggestion) but it errors out on capture (we use an Archive Log Downstream
Capture architecture). It appears that this procedure looks for the
existence of the table which doesn't really exist in the downstream
database.
GCSTRDEV_SQL > BEGIN
2 DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
3 table_name => 'SATURN.SSBSECT',
4 dml_condition => ' substr(SSBSECT_TERM_CODE,6,1) in (''2'',''6'',''9'') ',
5 streams_type => 'CAPTURE',
6 streams_name => 'STRM_CAPTURE_GC',
7 queue_name => 'STRM_CAPTURE_Q_GC');
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1032
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1181
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1073
ORA-06512: at line 2
On the apply however it creates fine (since that is the target and the table
exists there) --
GCDEV_SQL > BEGIN
2 DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
3 table_name => 'SATURN.SSBSECT',
4 dml_condition => ' substr(SSBSECT_TERM_CODE,6,1) in (''2'',''6'',''9'') ',
5 streams_type => 'APPLY',
6 streams_name => 'STRM_APPLY_GC',
7 queue_name => 'STRM_APPLY_Q_GC');
8 END;
9 /
PL/SQL procedure successfully completed.
This adds 3 new rules to the list of rules for this table.
Questions:
1) How do I make this work for the capture side (so that we filter capture
itself)?
2) Why is it adding 3 rules there (can be seen in dba_streams_table_rules)?
TIA
- Ravi Gaur
Lead Systems DBA
Univ of IL.
Other related posts:
- » Filter data in Oracle Streams
