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