PHP/PDO Call procedure with output CLOB in json

Question

How can I have the output CLOB from my stored procedure when I call it with PDO driver in PHP?

The procedure is define like this in Oracle:

PROCEDURE COPI_SCHE(P_ID    IN   LOCT_SCHE.ID%TYPE,
                     P_SCHE  OUT  CLOB)

Where LOCT_SCHE.ID%TYPE is NUMBER (10)

If I call it with TOAD it works:


    DECLARE
    test CLOB;
    BEGIN
    LOC_SH.COPI_SCHE(884, TEST);
    dbms_output.put_line(test);
    END

;

The output result:

{ "ID" : "915", "FK_RA_NO_PERM" : "1234567", "TYPE_SCHE" : "PLP", "DESC_SCHE" : "test 4", "AN" : "", "TIMB_MAJ" : "15-04-10", "USAG_MAJ" : "USER" }

So, in PHP, I try this:


    $connection = $this->getConnection();
    $idGrouping = 884;
    $sql = "CALL LOC_SH.COPI_SCHE(?,?)";        
    $statement = $connection->prepare($sql);
    $statement->bindParam(1, $idGrouping, PDO::PARAM_INT);
    $statement->bindParam(2, $result, PDO::PARAM_LOB);
    $statement->execute();

But Oracle throw:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'COPI_SCHE'

What's wrong?

I try many associations with PDO::PARAM_LOB|PDO::PARAM_INPUT_OUTPUT but nothing works.

Thanks for help.

Michael Michael · Accepted Answer · 2015-04-14T13:06:01

Finally got it by myself.

I don't know why but PDO::PARAM_LOB doesn't work in this case.

The solution is to use PDO::PARAM_STR like that:

$statement->bindParam(2, $result, PDO::PARAM_STR, 2048);

PHP/PDO Call procedure with output CLOB in json

1 Answers