We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Call stored procedure with CLOB output using PDO

Hi,

I'm trying to call a stored procedure with an Output CLOB variable.

First parameter is a number, second parameter is the output CLOB (to contain a json).

How can I bind my parameter with phalcon to get this output.

I try:

    $sql = "CALL MYPROC(?,?)";      

    $statement = $connection->prepare($sql);
    $statement->bindParam(1, intval($idGrouping), PDO::PARAM_INT);
    $statement->bindParam(2, $result, PDO::PARAM_LOB);
    $statement->execute();

  return $result;

Where

PROCEDURE MYPROC(P_ID IN NUMBER(10), P_SCHE OUT CLOB)

If I call it with TOAD it works:

DECLARE

test CLOB;

BEGIN

MYPROC(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" }

But in PHP I got:

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

My database is Oracle. I need to use PDO because my code will also be called with a postgresql database.

if I try

$success = $connection->execute("BEGIN MYPROC(?,?); END;", array( intval($idGrouping), $result), array(PDO::PARAM_INT, PDO::PARAM_LOB));

I got

General error: 6550 OCIStmtExecute: ORA-06550: Ligne 1, colonne 7 : PLS-00306: num�ro ou types d'arguments erron�s dans appel � 'COPI_SCHE' ORA-06550: Ligne 1, colonne 7 : PL/SQL: Statement ignored (/home/sersar01_adm/PDO_OCI-1.0/oci_statement.c:142)

Thanks



3.2k
Accepted
answer

Finaly got it.

I don't know why but I can't use PARAM_LOB.

If I use:

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

I got my $result correctly.