FAQ
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.

Search Discussions

  • Alexander Fatkulin at Jul 20, 2007 at 4:05 pm
    Krishna,

    can you show the full text of the rule?

    Why you didn't use (DEPTNO = 10 OR (DEPTNO>=501 and DEPTNO <= 600)) ?
    On 7/20/07, krishna sarabu wrote:
    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)
    --
    Alex Fatkulin,
    The Pythian Group,
    http://www.pythian.com/blogs/author/alexf/
    --
    http://www.freelists.org/webpage/oracle-l
  • Krishna sarabu at Jul 20, 2007 at 4:31 pm
    Thanks Alex.

    Please see below for the actual subset rule call... Unfortunately the requirement is to use the random values list and we can't simplify it.. That sample call was just prepared to reproduce the issue..

    BEGIN

    DBMS_STREAMS_ADM.ADD_SUBSET_RULES(

    table_name => 'strms_test.nts_derivative_ts',
    dml_condition => 'AGI_ID IN (8700,8720,8750,8760,8770,8780,8790,8800,8810,8820,8830,8840,8850,8860,8870,8930,8940,
    8950,8960,8970,9010,9020,9050,9070,9080,9090,9100,9110,9120,9130,9140,9150,9160,9170,9180,
    9190,9200,9210,9220,9230,9240,9250,9260,9280,9290,9300,9340,9380,9390,9400,9410,9420,9430,
    9440,9450,9460,9470,9490,9550,9580,9590,9610,9640,9650,9660,9720,9820,9830,9850,9860,9870,
    9880,9900,9920,9930,9940,9950,9970,9980,9990,10010,10020,10030,10050,10070,10080,10090,10100,
    10110,10120,10140,10150,10170,10180,10190,10200,10210,10220,10230,10260,10270,10280,10320,
    10330,10340,10350,10360,11210,11290,11300,12420,3340373,3340401,3340426,4000285,4000286,4000289,
    4000291,4000297,4000298,4000301,4000307,4000308,4000314,4000315,4000316,4000317,4000321,4000322,
    4000323,4000324,4000325,4000326,4000327,4000328,4000362,4000389,4669501,4669568,4669569,4669570,
    4669571,4669581,5345135,5356001,5356002,5356003,5358715,5358756,5366965,5396063,5401435,5418021,
    5426337,5450297,5455797,5507811,5507818,5513940,5513941,5513948,5513949,5513950,5543011,5543022,
    5571228,5604007,5609411,5622904,5628516,5657811,5658706,5658713,5692808,5703815,5709007,5717009,
    5724402,5724403,5768724,5769004,5791708,5796308,5812908,5818911,5833308,5843121,5854507,5865835,
    5865840,5404303,5409751,5422276,5836523,5893256,5893261,5893277,5920622,5958209,6009008,6009010,

    6180007,3060,2960,2980,6240,3340404,3050,5344188,5376523,5727004,5727054,5743007,5774647,5355994,
    5366626,5372551,5376503,5376523,5381770,5384792,5408848,5648405,5698602,5700316)',

    streams_type => 'CAPTURE',
    streams_name => 'STST_CAPT01',
    queue_name => 'STRMADMIN.CNRTS1_Q');

    END;

    /

    24 BEGIN

    *
    ERROR at line 1:

    ORA-25448: rule STRMADMIN.NTS_DERIVATIVE_TS123 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 2

    Regards,
    Krishna

    Alexander Fatkulin wrote: Krishna,

    can you show the full text of the rule?

    Why you didn't use (DEPTNO = 10 OR (DEPTNO>=501 and DEPTNO <= 600)) ?
    On 7/20/07, krishna sarabu wrote:
    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)
    --
    Alex Fatkulin,
    The Pythian Group,
    http://www.pythian.com/blogs/author/alexf/
    --
    http://www.freelists.org/webpage/oracle-l

    ---------------------------------
    Got a little couch potato?
    Check out fun summer activities for kids.
    --
    http://www.freelists.org/webpage/oracle-l
  • Alexander Fatkulin at Jul 20, 2007 at 5:48 pm
    Do a ctrl-f for "536696 5"

    you have a typo
  • Krishna sarabu at Jul 20, 2007 at 6:09 pm
    Hi Alex,

    Thanks. That was issue with e-mail formatting.. The actual sql looks fine..

    set serveroutput on
    DECLARE

    v_Cnt Number;

    v_Condn Varchar2(2000) := 'AGI_ID IN (8700,8720,8750,8760,8770,8780,8790,8800,8810,8820,8830,8840,8850,8860,8870,8930,8940,
    8950,8960,8970,9010,9020,9050,9070,9080,9090,9100,9110,9120,9130,9140,9150,9160,9170,9180,
    9190,9200,9210,9220,9230,9240,9250,9260,9280,9290,9300,9340,9380,9390,9400,9410,9420,9430,
    9440,9450,9460,9470,9490,9550,9580,9590,9610,9640,9650,9660,9720,9820,9830,9850,9860,9870,
    9880,9900,9920,9930,9940,9950,9970,9980,9990,10010,10020,10030,10050,10070,10080,10090,10100,
    10110,10120,10140,10150,10170,10180,10190,10200,10210,10220,10230,10260,10270,10280,10320,
    10330,10340,10350,10360,11210,11290,11300,12420,3340373,3340401,3340426,4000285,4000286,4000289,
    4000291,4000297,4000298,4000301,4000307,4000308,4000314,4000315,4000316,4000317,4000321,4000322,
    4000323,4000324,4000325,4000326,4000327,4000328,4000362,4000389,4669501,4669568,4669569,4669570,
    4669571,4669581,5345135,5356001,5356002,5356003,5358715,5358756,5366965,5396063,5401435,5418021,
    5426337,5450297,5455797,5507811,5507818,5513940,5513941,5513948,5513949,5513950,5543011,5543022,
    5571228,5604007,5609411,5622904,5628516,5657811,5658706,5658713,5692808,5703815,5709007,5717009,
    5724402,5724403,5768724,5769004,5791708,5796308,5812908,5818911,5833308,5843121,5854507,5865835,
    5865840,5404303,5409751,5422276,5836523,5893256,5893261,5893277,5920622,5958209,6009008,6009010,

    6180007,3060,2960,2980,6240,3340404,3050,5344188,5376523,5727004,5727054,5743007,5774647,5355994,
    5366626,5372551,5376503,5376523,5381770,5384792,5408848,5648405,5698602,5700316)';

    BEGIN

    execute immediate 'select count(1) from strms_test.nts_derivative_Ts where '||v_condn into v_cnt;

    dbms_output.put_line('# Of Rows in Table : '|| v_Cnt);

    DBMS_STREAMS_ADM.ADD_SUBSET_RULES(

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

    END;

    /

    # Of Rows in Table : 175615462
    DECLARE

    *
    ERROR at line 1:

    ORA-25448: rule STRMADMIN.NTS_DERIVATIVE_TS132 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 25

    Alexander Fatkulin wrote: Do a ctrl-f for "536696 5"

    you have a typo

    --
    Alex Fatkulin,
    The Pythian Group,
    http://www.pythian.com/blogs/author/alexf/
    --
    http://www.freelists.org/webpage/oracle-l



    Boardwalk for $500? In 2007? Ha!
    Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
    --
    http://www.freelists.org/webpage/oracle-l
  • Alexander Fatkulin at Jul 20, 2007 at 6:40 pm
    I'm not sure what we are hitting here but there is clearly some sort
    of an issue...

    What I would do is create a separate rule instead (add_subset_rule
    will end up creating a three rules (update/insert/delete) under the
    covers anyway).

    Create rule and attach it to capture rule set. This will effectively
    do the same.

    If you are not sure how they should look like - create them using
    add_subset_rules with a shorter list and then modify using
    dbms_rule_adm.alter_rule.

    I checked it this way - it works (at least rule was created without an error).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 20, '07 at 3:19p
activeJul 20, '07 at 6:40p
posts6
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase