Streams Subset-Rule issue

From: krishna sarabu <ksarabu_at_yahoo.com>
Date: Fri, 20 Jul 2007 08:19:55 -0700 (PDT)
Message-ID: <666674.51395.qm@web56206.mail.re3.yahoo.com>

Hi,

We are trying to setup a subset rule on source table using 200+ in-list values and experiencing ORA-936 errors, not sure if it is a bug/restriction. Have checked Oracle docs and found no restriction on in-list values in subset rules. Below is the error message: (Please note that the source/destination databases are on 10.2.0.2 and we are planning to use dedicated capture process/queue for this table)

subset rule with 60+ in-list values.. @setup_subset_rule_test2.sql

DECLARE
*
ERROR at line 1:

ORA-25448: rule STRMADMIN.NTS_DERIVATIVE_TS66 has errors
ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 49
ORA-06512: at "SYS.DBMS_RULE_ADM", line 178
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1205
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 1073
ORA-06512: at line 20
======================================================

From the 10046 trace on above call, seems that the ORA-936 error was due to the sql (attached below) parse issue. The "missing" where condition in "WHERE" clause causing parsing error and returning ORA-936 error. Appear to be a BUG/restriction in Oracle.

PARSE ERROR #11:len=65 dep=1 uid=0 oct=3 lid=88 tim=9534560440959 err=936 SELECT /*+ cursor_sharing_exact */ count(*) FROM SYS.DUAL WHERE XCTEND rlbk=1, rd_only=1
EXEC #1:c=440000,e=474860,p=2,cr=519,cu=9,mis=0,r=0,dep=0,og=1,tim=9534560442534 ERROR #1:err=25448 tim=976338989

Anyways, we have tried using alternate workaround with stored function that validates the given value instead, as mentioned below. But, the stored function call in the rule making it Complex and severely impacting the capture performance (from the tests we noticed that the Capture process is over 8 times slower).

Wondering if any of you have seen similar issue before and if you know any alternate efficient method like using our own evaluation contexts as this capture/ruleset is dedicated for this table. Appreciate your help and time.

Create Or Replace Function strmadmin.Chk_Val(p_Agi In Number) Return Varchar2 DETERMINISTIC IS Begin

If p_Agi In (10,501,502,503,504,505,506,507,508,509,510

,511,512,513,514,515,516,517,518,519,520
,521,522,523,524,525,526,527,528,529,530
,531,532,533,534,535,536,537,538,539,540
,541,542,543,544,545,546,547,548,549,550
,551,552,553,554,555,556,557,558,559,560
,561,562,563,564,565,566,567,568,569,570
,571,572,573,574,575,576,577,578,579,580
,581,582,583,584,585,586,587,588,589,590
,591,592,593,594,595,596,597,598,599,600)
Then

Return 'Y' ;
Else

Return 'N' ;
End If;
End;
/

DECLARE
v_Condn Varchar2(2000) := 'strmadmin.chk_val(DEPTNO) = ''Y'''; BEGIN
DBMS_STREAMS_ADM.ADD_SUBSET_RULES(

        table_name => 'strms_test.strms_subset_test1',
        dml_condition => v_Condn,
        streams_type => 'CAPTURE',
        streams_name => 'STST_CAPT01',
        queue_name => 'STRMADMIN.CNRTS1_Q');

END;
/

Thanks for your help in advance.

Regards,
Krishna

Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 20 2007 - 10:19:55 CDT