[Oracle] DBAL 4 Bind outputs for stored procedure

[philippedasilva]

Summary

Hello,
In previous versions (dbal < 4) I used "Doctrine\DBAL\Driver\OCI8\bindParam" to assign inputs and outputs on an Oracle procedure and it worked fine!

But since version 4, the method "Doctrine\DBAL\Driver\OCI8\bindParam" has been removed.
But the method "Doctrine\DBAL\Driver\OCI8\bindValue" doesn't seem to have the output option! I can't get my outputs after calling my procedure.

Do you have an alternative method/process to get the outputs from the procedure ?

Current behavior

With "bindValue" I can easily assign my inputs of procedure.
But my outputs are always null !

Exemple of code :

CREATE OR REPLACE PACKAGE BODY "MYPACKAGE"
IS
   PROCEDURE MYPROC(p_input1 IN VARCHAR2, p_input2  IN VARCHAR2, p_output1 OUT VARCHAR2, p_output2 OUT VARCHAR2)
   IS
        BEGIN
             p_output1 := 'output1';
             p_output2 := 'output2';
        END;
   END:
END;

$stmt = $this->connection->prepare("CALL MYPACKAGE.MYPROC(:p_input1, :p_input2, :p_output1, :p_output2)");
$input1 = "input1";
$input2 = "input2";
$output1 = $output2 = null;
$stmt->bindValue('p_input1', $input1, \PDO::PARAM_STR, 255);
$stmt->bindValue('p_input2', $input2, \PDO::PARAM_STR, 255);
$stmt->bindValue('p_output1', $output1, \PDO::PARAM_STR, 255);
$stmt->bindValue('p_output2', $output2, \PDO::PARAM_STR, 255);
$stmt->execute();

Expected behavior

var_dump($output1); --Expected to have 'output1' as value
In "bindParam" method, the passed value is a reference but not in "bindValue" method.

[nbonneau]

Hello,

We have the same problem here.

As mentioned by @philippedasilva the method bindValue does not pass the $value by reference, so the output binding is not possible.

We can see it in file Doctrine\DBAL\Statement at line 75:

public function bindValue( string|int $param, mixed $value, string|ParameterType|Type $type = ParameterType::STRING, ): void {

In addition, in file Doctrine\DBAL\Driver\OCI8\Statement the maxLength parameter for the oci_bind_by_name call is set to -1, so even if the $value is by reference, the output will be truncated.

@oci_bind_by_name( $this->statement, $param, $value, -1, $this->convertParameterType($type)

Is it possible to fix this or do you have any workaround for this issue ?

Thx

[greg0ire]

Link to the deprecating PR: #5563

[morozov]

Prepared statements (which is the only valid case for output parameters I can think of) is not part of the database abstraction defined by the library, and we cannot support every feature of every driver and platform here.

Please consider using Connection#getNativeConnection() and the oci8 API directly.

[nbonneau]

Hello,

Thank for your reply.

I think if you pass the mixed $value by reference into the bindValue method, and add a optional maxLength parameter will resolve the issue.