added a comment - - 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)))
{
}
foreach ($value as $entry)
{
$list->append($entry);
}
if (!oci_bind_by_name($this->_sth, $param, $list, -1, OCI_B_NTY))
{
}
}
// 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;
Bug
Open
Critical
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;