Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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(PID IN NUMBER(10), PSCHE OUT CLOB)

If I call it with TOAD it works:

DECLARE

test CLOB;

BEGIN

MYPROC(884, TEST);

dbmsoutput.putline(test);

END

The output result:

{ "ID" : "915", "FKRANOPERM" : "1234567", "TYPESCHE" : "PLP", "DESCSCHE" : "test 4", "AN" : "", "TIMBMAJ" : "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::PARAMINT, PDO::PARAMLOB));

I got

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

Thanks



1.8k
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.