My Oracle Support Banner

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 FEBRUARY 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.4 [Release 10.1 to 10.2]
Information in this document applies to any platform.
This problem can occur on any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

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