Skip to content
YajraBox

Laravel Oci8
Icon

WARNING You're browsing the documentation for an upcoming version of Laravel Oci8. The documentation and features of this release are subject to change.

Oracle Cursors

Cursors allow you to return result sets from stored procedures and functions. This guide shows how to work with cursors in Laravel-OCI8.

Returning a Cursor from a Function

SQL: Create the Function

CREATE OR REPLACE FUNCTION mycursor(p1 IN NUMBER) RETURN SYS_REFCURSOR AS
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR SELECT city FROM locations WHERE ROWNUM < p1;
    RETURN rc;
END;

PHP: Fetch the Cursor Result

Using the Query Builder:

$result = DB::select("SELECT mycursor(5) AS mfrc FROM dual");
 
return $result[0]->mfrc;

Using PDO directly:

$pdo = DB::getPdo();
$stmt = $pdo->prepare("SELECT mycursor(5) AS mfrc FROM dual");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
 
return $result[0]->mfrc;

Returning a Cursor from a Stored Procedure

The following example demonstrates calling a procedure that returns a cursor via an OUT parameter, as contributed by the community.

$sql = "BEGIN
    sgc.pintegracaomodoffline.ListaCidade(:pTabResultado, :pCodRetorno, :pMsgRetorno);
END;";
 
return DB::transaction(function ($conn) use ($sql) {
    $pdo = $conn->getPdo();
    $stmt = $pdo->prepare($sql);
 
    // Bind output parameters
    $stmt->bindParam(':pTabResultado', $lista, PDO::PARAM_STMT);
    $stmt->bindParam(':pCodRetorno', $cod, PDO::PARAM_INT);
    $stmt->bindParam(':pMsgRetorno', $text, PDO::PARAM_STR, 100);
 
    $stmt->execute();
 
    // Execute the cursor
    oci_execute($lista, OCI_DEFAULT);
    oci_fetch_all($lista, $array, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
    oci_free_cursor($lista);
 
    return $array;
});

Note: This example uses raw OCI8 functions (oci_execute, oci_fetch_all, oci_free_cursor) for maximum control over cursor handling. For simpler use cases, consider using the function shortcut method.

Tips

  • Always execute cursors within a transaction when modifying data
  • Use OCI_DEFAULT mode when you need to control commit behavior manually
  • Remember to free cursor resources with oci_free_cursor() to prevent memory leaks

See Also

Edit this page on GitHub