Yii2: Getting ORA-01460 when binding several string parameters

[fen1xpv]

What steps will reproduce the problem?

$sql =
<<<SQL
    select 1
    from dual
    where    '1' = :param_1
        and '22' = :param_2
SQL;

$cmd = Yii::$app->db->createCommand($sql);
$cmd->bindValue(':param_1', '1');
$cmd->bindValue(':param_2', '22');
$result = $cmd->queryAll();

What is the expected result?

[{"1":"1"}]

What do you get instead?

SQLSTATE[HY000]: General error: 1460 OCIStmtExecute: ORA-01460: затребовано нереализованное или неразумное преобразование
(/tmp/php-7.0.18/ext/pdo_oci/oci_statement.c:159)
The SQL being executed was: select 1
from dual
where '1' = '1'
and '22' = '22'

Stacktrace

PDOException: SQLSTATE[HY000]: General error: 1460 OCIStmtExecute: ORA-01460: затребовано нереализованное или неразумное преобразование
 (/tmp/php-7.0.18/ext/pdo_oci/oci_statement.c:159) in /var/www/html/vendor/yiisoft/yii2/db/Command.php:1293
Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1293): PDOStatement->execute()
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1159): yii\db\Command->internalExecute('    select 1\r\n ...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Command.php(401): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /var/www/html/models/Test2.php(23): yii\db\Command->queryAll()
#4 /var/www/html/controllers/TestController.php(17): app\models\Test2->getData()
#5 [internal function]: app\controllers\TestController->actionIndex()
#6 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#7 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#8 /var/www/html/vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('', Array)
#9 /var/www/html/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('test/', Array)
#10 /var/www/html/vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#11 /var/www/html/web/index.php(12): yii\base\Application->run()
#12 {main}

Next yii\db\Exception: SQLSTATE[HY000]: General error: 1460 OCIStmtExecute: ORA-01460: затребовано нереализованное или неразумное преобразование
 (/tmp/php-7.0.18/ext/pdo_oci/oci_statement.c:159)
The SQL being executed was:     select 1
    from dual
    where '1' = '1'
        and '22' = '22' in /var/www/html/vendor/yiisoft/yii2/db/Schema.php:674
Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1298): yii\db\Schema->convertException(Object(PDOException), '    select 1\r\n ...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1159): yii\db\Command->internalExecute('    select 1\r\n ...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Command.php(401): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /var/www/html/models/Test2.php(23): yii\db\Command->queryAll()
#4 /var/www/html/controllers/TestController.php(17): app\models\Test2->getData()
#5 [internal function]: app\controllers\TestController->actionIndex()
#6 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#7 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#8 /var/www/html/vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('', Array)
#9 /var/www/html/vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('test/', Array)
#10 /var/www/html/vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#11 /var/www/html/web/index.php(12): yii\base\Application->run()
#12 {main}
Additional Information:
Array
(
    [0] => HY000
    [1] => 1460
    [2] => OCIStmtExecute: ORA-01460: затребовано нереализованное или неразумное преобразование
 (/tmp/php-7.0.18/ext/pdo_oci/oci_statement.c:159)
)

Comments

This bug(?) occurs only on yii2 2.0.33, it works fine with 2.0.32.
I've got same behaviour on machine with php5.6 + Debian 7
If I bind parameters as Integers instead of Strings - problem goes away.
As far as I understand this issue may be related to changes in #17854

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.33
| PHP version | 7.0.33
| Operating system | Debian GNU/Linux 9
| Oracle version | 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
| Oracle Instant Client Version | 12.1.0.2.0

[samdark]

@vinpel, @243083df would you please take a look?

[fen1xpv]

While digging into this issue I've looked into this method in yii\db\oci\Command:

protected function bindPendingParams()
{
    foreach ($this->pendingParams as $name => $value) {
        if (\PDO::PARAM_STR === $value[1]) {
            $passedByRef = $value[0];
            $this->pdoStatement->bindParam($name, $passedByRef, $value[1], strlen($value[0]));
        } else {
            $this->pdoStatement->bindValue($name, $value[0], $value[1]);
        }
    }
    $this->pendingParams = [];
}

I'm not entirely sure how PDOStatement::bindParam works internally, but php docs says :

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

In case when I have several String parameters $passedByRef changes it's value during foreach loop, and this may be the source of the problem.

In my case problem is solved by changing method into this:

protected function bindPendingParams()
{
    $passedByRefParams = [];
    foreach ($this->pendingParams as $name => $value) {
        if (\PDO::PARAM_STR === $value[1]) {
            $passedByRefParams[$name] = $value[0];
            $this->pdoStatement->bindParam($name, $passedByRefParams[$name], $value[1], strlen($value[0]));
        } else {
            $this->pdoStatement->bindValue($name, $value[0], $value[1]);
        }
    }
    $this->pendingParams = [];
}

[samdark]

You are correct, @fen1xpv.