DBMS_STREAMS_ADM.ADD_SUBSET_RULES Throws ORA-6525 ORA-25448 ORA-936 for DML Condition with Large IN list values (Doc ID 751775.1)

Last updated on AUGUST 18, 2021

Applies to:

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. 

In streams environment,adding subset rule for stream processes may lead to following error for DML condition with large IN list values

ORA-06525, ORA-25448 and ORA-00936

For example:-When executing the following function with a count of >= 60 of the IN clause of DBMS_STREAMS_ADM.ADD_SUBSET_RULES values, it is throwing ORA-936.

Begin
DBMS_STREAMS_ADM.ADD_SUBSET_RULES(table_name => 'strms_test.strms_subset_test1',
dml_condition => 'DEPTNO 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)',
streams_type => 'CAPTURE',
streams_name => 'STST_CAPT01',
queue_name => 'STRMADMIN.CNRTS1_Q');
END;

ERROR:-
ERROR at line 1:
ORA-25448: rule STRMADMIN.STRMS_SUBSET_TEST125 has e
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

--------------------------------

When tried executing the  function with a count of  20 of the IN clause of DBMS_STREAMS_ADM.ADD_SUBSET_RULES values, it is successfully completed.

SQL> Begin
DBMS_STREAMS_ADM.ADD_SUBSET_RULES(table_name => 'strms_test.strms_subset_test1',
dml_condition => 'DEPTNO IN (10, 501, 502, 503, 504, 505,506,507, 508, 509, 510,
511, 512, 513,514, 515, 516, 517, 518, 519)',
streams_type => 'CAPTURE',
streams_name => 'STST_CAPT01',
queue_name => 'STRMADMIN.CNRTS1_Q');
END;

PL/SQL procedure successfully completed.

Analysis
---------

10.2.0.2/Linux
===========

dml_condition -->  inlist with

21 values - succeed
22 values - First exec : ORA-06525: Length Mismatch for CHAR or RAW data
Repeated exec : ORA-06525: Length Mismatch for CHAR or RAW data
56 or more - First exec : ORA-06525: Length Mismatch for CHAR or RAW data
values Repeated exec : ORA-06525: Length Mismatch for CHAR or RAW data

10.2.0.2/Solaris
============

dml_condition --> inlist with

21 values - succeed
22 values - First exec : ORA-06525: Length Mismatch for CHAR or RAW data
Repeated exec : ORA-06525: Length Mismatch for CHAR or RAW data
56 or more - First exec : ORA-06525: Length Mismatch for CHAR or RAW data
values Repeated exec : ORA-25448: rule SYS.STRMS_SUBSET_TEST190 has
errors
ORA-00936: missing expression

Behaviour is different for solaris and linux .

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.