Execute stored procedure with multiple cursors

[mhelaiwa]

Summary of problem or feature request

I have a stored procedures with more than one cursor two or even more and wonder how could I execute such a stored procedure?

procedure MyProcedure(p1 in number, p2 out number,
records1 out sys_refcursor,records2 out sys_refcursor);

Code snippet of problem

The problem is that the available functionality from this package is to execute procedure with only one cursor.

public function executeProcedureWithCursor($procedureName,
array $bindings = [], $cursorName = ':cursor'){
.....
}

• Windows 10 pro
• PHP 7.1
• Laravel 5.6.33
• Laravel-OCI8 5.6.*

[yajra]

Try executing it manually. See https://yajrabox.com/docs/laravel-oci8/master/stored-procedure for ref. Thanks!

[mhelaiwa]

Here is my solution for this question if anyone needs that: I'v made a class ODB with executeProcedure function that takes procedure name and bindings as inputs.

Here is my procedure's declaration

procedure GetUsers2(p1 in varchar2, p2 out number,records out sys_refcursor,records2 out sys_refcursor);

Here is the binding

$bindings = [
        ['name' => 'id','value' => 12],         // input of integer type. You could simply pass 12 directly
        ['name' => 'result','type' => ODB::INTEGER],       // output parameter of type integer.
        ['name' => 'users','type' => ODB::CURSOR],     // output parameter of type cursor
        ['name' => 'roles','type' => ODB::CURSOR],     // output parameter of type cursor
    ];
    $res = ODB::executeProcedure('test.GetUsers2', $bindings);

namespace App\Models;

use App\Facads\ODB;
use Illuminate\Support\Facades\DB;

class OracleDatabase //extends Oci8Connection
{

    public function executeProcedure($procedureName, array $bindings = []){
        $pdo = DB::getPdo();
        $paramNames = implode(',:',array_pluck($bindings,'name'));
        $stmt = $pdo->prepare("begin $procedureName(:$paramNames); end;");
        $cursors = [];
        $result = [];

        foreach ($bindings as $key => $row){
             // binding input parameters.
            if(isset($row['value']))
                $stmt->bindParam(":".$row['name'], $row['value']);
            
            // binding cursor output parameters.
            else if($row['type'] === ODB::CURSOR)
                $stmt->bindParam(":".$row['name'],  $cursors[$row['name']], ODB::CURSOR);

            // binding other none cursor output parameters.
            else
                $stmt->bindParam(":".$row['name'],  $result[$row['name']], $row['type']);
        }

        $stmt->execute();

        //fetch cursors and put them inside result
        foreach($cursors as $key => $cursor){
            oci_execute($cursor, OCI_DEFAULT);
            oci_fetch_all($cursor, $result[$key], 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC );
            oci_free_cursor($cursor);
        }

        return $result;
    }
}

ODB is a laravel facade that holds a reference to my class and a couple of constants for bindings that encapsulate PDO types and easier to remember.

namespace App\Facads;


use Illuminate\Support\Facades\Facade;

class ODB extends Facade {
    protected static function getFacadeAccessor() { return 'odb'; }
    const INTEGER = \PDO::PARAM_INT;
    const STRING = \PDO::PARAM_STR;
    const CHAR = \PDO::PARAM_STR_CHAR;
    const BOOLEAN = \PDO::PARAM_BOOL;
    const LOB = \PDO::PARAM_LOB;
    const CURSOR = \PDO::PARAM_STMT;
}

This works very well for me. Any help to improve this code will be appreciated :) thank you

[luis-eah]

This could help you

/**
* Procedure
*/
create or replace PROCEDURE TEST
(
P_LENGHT IN NUMBER, 
NUM out NUMBER,
C1 out SYS_REFCURSOR, 
C2 out SYS_REFCURSOR, 
)
AS 
BEGIN

SELECT COUNT(TABLE_NAME) 
INTO NUM 
FROM USER_TABLES;

OPEN C1 FOR SELECT * FROM USER_TABLES WHERE LENGTH(TABLE_NAME) = P_LENGHT;
OPEN C2 FOR SELECT * FROM USER_TABLES;

END TEST;

use DB;
use PDO;

/*
* PHP
*/

public function executeProcedureWithCursor($procedureName,  $bindings)
{
    $cursors = [];
    $result = [];
    $pdo = DB::getPdo();
    $command  = sprintf('begin %s(:%s); end;', $procedureName, implode(', :', array_pluck($bindings, 'name')));
    $stmt = $pdo->prepare($command);

    foreach ($bindings as $key => $row) {
        if (isset($row['value']))
            $stmt->bindParam(":" . $row['name'], $row['value'], $row['type']);
        else
            $stmt->bindParam(":" . $row['name'],  $result[$row['name']], $row['type']);

        if( $row['type'] === PDO::PARAM_STMT) 
            $cursors[$row['name']] = $result[$row['name']]; 
    }
    $stmt->execute();
    $stmt->closeCursor();
   
    foreach ($cursors as $key => $cursor) {
        oci_execute($cursor, OCI_DEFAULT);
        oci_fetch_all($cursor, $result[$key], 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
        oci_free_cursor($cursor);
    }

    return $result;
}

public function getInfoProcedure()
{
    $procedureName = 'TEST';

    $bindings = [
        ['name' => 'P_LENGHT', 'value' => 12, 'type' => PDO:: PARAM_INT],        
        ['name' => 'NUM', 'type' => PDO::PARAM_INT],    // output parameter of type integer.
        ['name' => 'C1', 'type' => PDO::PARAM_STMT],    // output parameter of type cursor
        ['name' => 'C2', 'type' => PDO::PARAM_STMT],    // output parameter of type cursor
    ];
    return $this->executeProcedureWithCursor($procedureName, $bindings);
}

[gvvenki]

Hi How i Loop the result at View Side After Get The Result

[gvvenki]

This could help you

/**
* Procedure
*/
create or replace PROCEDURE TEST
(
P_LENGHT IN NUMBER, 
NUM out NUMBER,
C1 out SYS_REFCURSOR, 
C2 out SYS_REFCURSOR, 
)
AS 
BEGIN

SELECT COUNT(TABLE_NAME) 
INTO NUM 
FROM USER_TABLES;

OPEN C1 FOR SELECT * FROM USER_TABLES WHERE LENGTH(TABLE_NAME) = P_LENGHT;
OPEN C2 FOR SELECT * FROM USER_TABLES;

END TEST;

use DB;
use PDO;

/*
* PHP
*/

public function executeProcedureWithCursor($procedureName,  $bindings)
{
    $cursors = [];
    $result = [];
    $pdo = DB::getPdo();
    $command  = sprintf('begin %s(:%s); end;', $procedureName, implode(', :', array_pluck($bindings, 'name')));
    $stmt = $pdo->prepare($command);

    foreach ($bindings as $key => $row) {
        if (isset($row['value']))
            $stmt->bindParam(":" . $row['name'], $row['value'], $row['type']);
        else
            $stmt->bindParam(":" . $row['name'],  $result[$row['name']], $row['type']);

        if( $row['type'] === PDO::PARAM_STMT) 
            $cursors[$row['name']] = $result[$row['name']]; 
    }
    $stmt->execute();
    $stmt->closeCursor();
   
    foreach ($cursors as $key => $cursor) {
        oci_execute($cursor, OCI_DEFAULT);
        oci_fetch_all($cursor, $result[$key], 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
        oci_free_cursor($cursor);
    }

    return $result;
}

public function getInfoProcedure()
{
    $procedureName = 'TEST';

    $bindings = [
        ['name' => 'P_LENGHT', 'value' => 12, 'type' => PDO:: PARAM_INT],        
        ['name' => 'NUM', 'type' => PDO::PARAM_INT],    // output parameter of type integer.
        ['name' => 'C1', 'type' => PDO::PARAM_STMT],    // output parameter of type cursor
        ['name' => 'C2', 'type' => PDO::PARAM_STMT],    // output parameter of type cursor
    ];
    return $this->executeProcedureWithCursor($procedureName, $bindings);
}

Hi How i Loop the result at View Side After Get The Result

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.