Skip to content

Loading…

DBAL-858: oracle IN statement with more than 1000 values #2095

Open
doctrinebot opened this Issue · 3 comments

2 participants

@doctrinebot

Jira issue originally created by user mdrolet:

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:
select * from test where id IN(' . implode(',', $values) . ')
and I've also try with executeQuery:
select * from test where id IN(:test)
executeQuery($sql, array($values), array(\Doctrine\DBAL\Connection::PARAMINTARRAY))

@doctrinebot

Comment created by mdrolet:

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:
listinttype 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: liststr_type and one for integer list_inttype

create or replace type liststrtype as table of varchar2(4000);
create or replace type listinttype as table of number;

@doctrinebot

Comment created by @beberlei:

Hey [~mdrolet]

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?

@doctrinebot

Comment created by mdrolet:

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.

@beberlei beberlei was assigned by doctrinebot
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.