Procedure about laravel-oci8 HOT 6 CLOSED

Hey guys!

Just to keep you posted, I've just pushed an update that is related to procedure and cursor on v2.3.0. Example usage can be found at https://github.com/yajra/laravel-oci8/wiki/Oracle-Cursor.

Regards!

from laravel-oci8.

Hi,

there's an other way for using the returned cursor:

use Yajra\Pdo\Oci8;
use Yajra\Pdo\Oci8\Statement;

$conn = new Oci8(
	'oci:dbname=localhost:1521/xe;charset=UTF8',
	'user',
	'pass'
);

$stmt = $conn->prepare("BEGIN TEST_PKG.GET_RECORDS(:cursor); END;");

$stmt->bindParam(':cursor', $cursor, PDO::PARAM_STMT);

$stmt->execute();

$cursor_stmt = new Statement($cursor, $conn, [
	PDO::ATTR_CASE => PDO::CASE_LOWER
]);

$cursor_stmt->execute();

foreach ($cursor_stmt->fetchAll(PDO::FETCH_ASSOC) as $item) {
	print_r($item);
}

$conn->closeCursor($cursor);

Thanks for your effort creating and maintaining this package!

Peter

from laravel-oci8.

Honestly haven't tried this on procedure yet but you can try using DB::select().

On the other hand, you can also use DB::transaction. Have used it for an oracle function.

DB::transaction(function($conn)
{
    $filename = Input::get('filename');
    $pdo = $conn->getPdo();
    $sql = "begin :ret := check_if_file_exists(:filename); end;";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':ret',$exists);
    $stmt->bindParam(':filename',$filename);
    $stmt->execute();

    if ($exists)
    {
    ....
    }
});

from laravel-oci8.

BTW, if the suggestions above did not work, please provide feedback for any error you may encounter. Will also try to use procedure on my free time. Thanks!

from laravel-oci8.

I made a solution that i'm pretty sure is not the best, but was able to use a stored procedure that returns a cursor by using (the cursor result will be in $array):

$sql = "begin sgc.pintegracaomodoffline.ListaCidade(:pTabResultado, :pCodRetorno, :pMsgRetorno); end;"

return DB::transaction(function($conn) use ($sql){
    $pdo = $conn->getPdo();
    $stmt = $pdo->prepare($sql);

    $stmt->bindParam(':pTabResultado', $lista, PDO::PARAM_STMT);
    $stmt->bindParam(':pCodRetorno', $cod, PDO::PARAM_INT);

    $stmt->bindParam(':pMsgRetorno', $text, PDO::PARAM_STR, 100);
    $stmt->execute();

    oci_execute($lista, OCI_DEFAULT);
    oci_fetch_all($lista, $array, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC );
    oci_free_cursor($lista);

    return $array;
});

from laravel-oci8.

@TiagoGoddard thanks for sharing your solution! This is a good reference when the time comes for me to use stored procedures too. 🍻

from laravel-oci8.