Hello,
in some places we use the "InOperator" class to specify a list of values for filtering.
As we know in these cases XPO creates an SQL statement that containes a where clause of the form "where x in (1,2,3,4,…)" from this operator.
As we also know Oracle has a constraint on the number of operands in the "in" expression that is 1000. If one creates a select statement with an in expression that containes i.e. 1001 values an SQL Error
SQL Error: ORA-01795: "maximum number of expressions in a list is 1000"
will be returned.
Unfortunately XPO doesent take care of this constraint and therefore creates failing sql statements if more than 1000 values are provided to the InOperator.
Could you change the behaviour of the Oracle ConnectionProvider so that it createes multiple combined in-expressions in case there have been more than 1000 operands in the in expression?
best regards and thanks
Florian
Proposed Solution:
Change the behaviour of the Oracle ConnectionProvider to create multiple combined in-expressions in case there are more than 1000 operands in an in expression?
For example:
Instead of creating a in expression with more than 1000 values
select * from Orders where OrderId in (1,2,3,4,5,6,7,…,1201,1202,1203)
create multiple in expressions that are combined with an or operator
select * from Orders where (OrderId in (1,2,3,…,998,999) or OrderId in (1000,1001,…1201,1202,1203))
We have closed this ticket because another page addresses its subject:
How to avoid the "Too many parameters..." error when querying large amounts of persistent objects by their keysAutomatically split queries with In-Operator when too many operands are involved
Answers approved by DevExpress Support
Hello Andreas,
Sure, we can. First we also thought that splitting would work, but later we wrote a test, which failed with the System.Data.OracleClient.OracleException:
ORA-03113: end-of-file on communication channel
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidD
escriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteReader()
at OracleIn.Program.Main(String[] args) in E:\work\OracleIn\Program.cs:line 24
This appears when about 14000 parameters are used.
While it seems that no generic solution exists for this particular scenario, these limitations of the database server can be covered by the end application depending on a precise business scenario and requirements.
See Also:
Maximum Capacity Specifications for SQL Server.
A problem occurs with the InOperator when the collection exceds 2100 items
Thanks,
Dennis
Hello Andres,
Just a follow up to my previous message. I think I should apologize, because my last sentence was not completely correct. By saying "covered by the end application", I wanted to say that there is no way to completely handle these situations at the XPO level, because there will always be some limitation at the database level. I believe that the best way to handle this situation is to either restructure the application business logic to prevent this criteria from being created or manually split long statements to separate collections and then merge them after required objects are loaded.
Please feel free to contact us if you have any additional questions related to this task. We'll be happy to help you.
Thanks,
Dennis
Hello Andreas,
Thanks for contacting us. We have discussed your suggestion with our XPO Team, and they stated that the variant with the OR operator won't work in a general case, and there is only one way to solve this problem - to split a large query into several smaller queries and then combine their results.
It is very difficult to implement this task in a general case and benefits from this functionality won't justify all the effort spent on the correct implementation of this feature. But, this task can be easily solved for particular cases in an end application.
Thanks,
Dennis
Hello Dennis,
can you explain why "the variant with the OR operator won't work in a general case"?
I think it should well work in a general case as actually a "x in (1,2,3,4)" expression can be translated to "x = 1 OR x = 2 OR x = 3 OR x = 4", exactly the same way that "x in (1,2) OR x in (3,4)" can be translated to the same expression "x = 1 OR x = 2 OR x = 3 OR x = 4".
This should always work no matter what x is or what or how many values are inside the value list.
Best regards
Florian