[DBAL-858] oracle IN statement with more than 1000 values Created: 11/Jan/13 Updated: 01/Apr/14
|Reporter:||Marc Drolet||Assignee:||Benjamin Eberlei|
If I have a query with a IN statement with more tahn 1000 values I get an sql error.
I've try IN with implode:
|Comment by Marc Drolet [ 11/Jan/13 ]|
Here is the way I've implement the solution on my side: (for oracle)
into Doctrine/DBAL/Statement.php, I've add this method:
into Doctrine/DBAL/Driver/Statement.php I've add:
into Doctrine/DBAL/Driver/OCI8/OCI8Statement.php I've add this method:
// NOTE: we should probably add the bindList to all driver Statement object.
into your code you can use it this way:
create or replace type list_str_type as table of varchar2(4000);
|Comment by Benjamin Eberlei [ 01/Apr/13 ]|
Hey Marc Drolet
thanks for the feedback and the solution, however i would like to have something generic that is working independent of the database driver. This code is very specific.
Can you point me to some documentation why oci collection works with more than 1000 elements and how it works in PHP?
|Comment by Marc Drolet [ 02/Apr/13 ]|
The limitation is not from the oci driver, it's an oracle limitation. There are a couple of possible solution/implementation that can be done but the one I've provide is the one that perform better for the test I've done and from what I can found over the blogs I've read.
I can't find the exact documentation of oracle. oracle doc is so poor.
I don't know if there is similar limitation with other database. With the implementation I've provided, It will be possible to implement the proper solution depending on the database limitation you face otherwise it will execute the generic IN. What's bad, we need to create the type into the database.
NOTE: In my case, I can not perform a sub-query, I get the my collection from a web service call.