oracle IN statement with more than 1000 values

Marc Drolet added a comment - 11/Jan/13 4:47 PM - edited Here is the way I've implement the solution on my side: (for oracle) into Doctrine/DBAL/Statement.php, I've add this method: /** * Binds a parameter value to the statement. * This is implemented this way for oracle only. Other drivers are redirected to bindValue method. * * The value will be bound with to the type provided (that required to be a table type). * * @param String $name The name or position of the parameter. * @param Array $value The value of the parameter. * @param String $type The name of the type to use to bind. * @ return boolean TRUE on success, FALSE on failure. */ public function bindList($name, Array $value, $type) { if ('oracle' !== $ this ->platform->getName()) { $ this ->bindValue($name, $value, $type); } else { return $ this ->stmt->bindList($name, $value, $type); } } into Doctrine/DBAL/Driver/Statement.php I've add: /** * @TODO: docs */ function bindList($param, Array $values, $type); into Doctrine/DBAL/Driver/OCI8/OCI8Statement.php I've add this method: /** * {@inheritdoc} */ public function bindList($param, Array $value, $type) { if (!($list = oci_new_collection($ this ->_dbh, $type))) { // throw new OCI8Exception::fromErrorInfo($ this ->errorInfo()); } foreach ($value as $entry) { $list->append($entry); } if (!oci_bind_by_name($ this ->_sth, $param, $list, -1, OCI_B_NTY)) { // throw new OCI8Exception::fromErrorInfo($ this ->errorInfo()); } } // NOTE: we should probably add the bindList to all driver Statement object. into your code you can use it this way: $sql = " SELECT * FROM test WHERE id IN ( SELECT * FROM ( CAST (: p_ids AS list_int_type) ) ) "; $stmt = connection->prepare($sql); $stmt->bindList(': p_ids', $ids, 'list_int_type'); $stmt->execute(); $rs = $stmt->fetchAll(PDO::FETCH_ASSOC); NOTE: list_int_type need to be a valid oracle data type. You can create one with the name you want. example: you can have 2 type of accepted array of values: integer and string let's say we create one for string named: list_str_type and one for integer list_int_type create or replace type list_str_type as table of varchar2(4000); create or replace type list_int_type as table of number;

Benjamin Eberlei added a comment - 01/Apr/13 9:44 PM 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?

Marc Drolet added a comment - 02/Apr/13 12:30 PM Hi Benjamin, 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. Here is the best description link I can provide that describe some possible implementation. http://vsadilovskiy.wordpress.com/substituting-a-collection-for-in-list-performance-study/ 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.