Yii 2 and stored procedure oracle

Yii 2.0 General Discussions
1

Hi good morning. I am trying to execute a stored procedure in oracle. The SP is as follows (

[b]Begin

  • Call the procedure

Identity.sp_consultaserialcita (prmserial =>: prmserial,

                              Prmcur => prmcur);

End;[/b]

)

I’m trying to call it from my SiteController.php to do the first tests, but I can not get results.

My code:

[b]public function actionIndex()

{


    $prmserial = 'C52923081161821';


    $prmcur = " ";





    //$sql = 'begin identidad.sp_consultaserialcita(prmserial => :prmserial,prmcur => :prmcur);end;';





    $sql = 'call identidad.sp_consultaserialcita(:prmserial,:prmcur)';





    $command = Yii::$app->db->createCommand($sql);


    $command->bindParam(":prmserial",$prmserial,PDO::PARAM_INT);


    $command->bindParam(":prmcur",$prmcur,PDO::CURSOR_FWDONLY);


    $cursor = $command->execute();


    print_r($cursor);





    return $cursor;


}

[/b]

This throws me the following error:

[b]Database Exception - yii \ db \ Exception

Error Info: Array

(

[0] => HY000


[1] => 6553


[2] => OCIStmtExecute: ORA-06553: PLS-306: number or types of erroneous arguments when calling 'SP_CONSULTASERIALCITA'

(Ext.sup.core_statement.c: 148)

)

(I.e.

Caused by: PDOException[/b]

2

I got the solution.

First: the connection has to be with OCI8, I use the bobsbg/yii2-oci2pdo extension to be able to connect to oracle (important).

This is my code:

[b]private $devuelve_cita = "begin identity.sp_consultaserialcita(prmserial => :prmserial, prmcur => :prmcur); end;";


    


public function consultarCita()


{


    $prmserial = "C52923081161821";





    $connection = Yii::$app->db;


    


    $command = $connection->createCommand($this->devuelve_cita);


    $command->bindParam(":prmserial", $prmserial, PDO::PARAM_STR);


    $command->bindParam(":prmcur", $prmcur, OCI_B_CURSOR);


    $rs = $command->queryAll();





    return $rs;





    //print_r($rs);exit;


}[/b]

This is the way to use an oracle cursor with YII2 B)