User Rule creation in Capture Process ( ORACLE Streams )
641578May 27 2008 — edited May 31 2008Hello everybody
I'm new at the oracle strems and I'm trying to find how can I add a user rule in a Capture Process.
When we use the DBMS_STREAMS_ADM Procedures to create de DDL and DML, it create a system rule that evaluates the entire associated table.
But what I need a rule that filter a specific field. For example, I have a table ZONAS with 3 fields ZONA_COD (VARCHAR2); ZONA_DESC(VARCHAR2) and ZONA_PRED(NUMERIC) in the source database, and in the destination database I have an identical table.
But I just want to propagate the INSERTED registers in the orig table when the filed ZONA_PRED is equal 7.
The WHERE clause should be something like: ' ZONA_PRED = 7'
I'm using Oracle 9i, and when I try to execute :
BEGIN
DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
table_name => 'TESTE.ZONAS,
dml_condition => 'ZONA_PRED =7',
streams_type => capture',
streams_name => 'STRMADMIN.STRMADMIN_TESTE_WORLD_APP',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_tagged_lcr => false,
source_database => 'ZONA.WORLD');
END;
/
I get :
ERRO na linha 1:
ORA-23605: valor inválido "capture" para o parâmetro STREAMS STREAMS_TYPE
ORA-06512: na "SYS.DBMS_SYS_ERROR", linha 95
ORA-06512: na "SYS.DBMS_STREAMS_ADM", linha 759
ORA-06512: na "SYS.DBMS_STREAMS_ADM", linha 701
ORA-06512: na linha 2
So how can I add my user rule to the capture, avoiding that way a large number of
LCRs in the queue?