Version

Oracle Stored Procedures

Stored procedures allow you to execute business logic directly in the Oracle database. This guide covers creating and calling stored procedures in Laravel-OCI8.

Prerequisites

Before calling a procedure from PHP, create it in your Oracle database using SQL*Plus, SQL Developer, or a Laravel migration.

Creating the Procedure

Using Plain SQL

CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
BEGIN
    p2 := p1 * 2;
END;

Using Laravel Migration

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
 
class CreateMyprocProcedure extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        $command = "
            CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
            BEGIN
                p2 := p1 * 2;
            END;
        ";
 
        DB::connection()->getPdo()->exec($command);
    }
 
    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::connection()->getPdo()->exec("DROP PROCEDURE myproc");
    }
}

Calling Procedures from PHP

Manual Method (PDO)

For maximum control over parameter binding:

$pdo = DB::getPdo();
$p1 = 8;
 
$stmt = $pdo->prepare("BEGIN myproc(:p1, :p2); END;");
$stmt->bindParam(':p1', $p1, PDO::PARAM_INT);
$stmt->bindParam(':p2', $p2, PDO::PARAM_INT);
$stmt->execute();
 
return $p2; // Returns: 16

Using the Shortcut Method

Laravel-OCI8 provides a convenient executeProcedure method:

$procedureName = 'youpackagename.yourprocedurename';
 
$bindings = [
    'user_id'  => $id,
];
 
$result = DB::executeProcedure($procedureName, $bindings);
 
dd($result);

Parameter Types

Input Parameters (IN)

CREATE OR REPLACE PROCEDURE get_user(
    p_user_id IN NUMBER,
    p_result OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_result FOR
        SELECT * FROM users WHERE id = p_user_id;
END;

$result = DB::executeProcedure('get_user', [
    'p_user_id' => 123,
]);

Output Parameters (OUT)

$procedureName = 'calculate_total';
 
$bindings = [
    'p_price'    => 19.99,
    'p_quantity'  => 5,
    'p_total'     => null, // OUT parameter - pass null
];
 
$result = DB::executeProcedure($procedureName, $bindings);
 
// The total is now in $bindings['p_total']

IN OUT Parameters

CREATE OR REPLACE PROCEDURE double_value(p_value IN OUT NUMBER) AS
BEGIN
    p_value := p_value * 2;
END;

$pdo = DB::getPdo();
$value = 10;
 
$stmt = $pdo->prepare("BEGIN double_value(:p_value); END;");
$stmt->bindParam(':p_value', $value, PDO::PARAM_INT | PDO::INPUT_OUTPUT);
$stmt->execute();
 
echo $value; // Returns: 20

Error Handling

Always wrap procedure calls in try-catch blocks:

use Illuminate\Support\Facades\DB;
use Exception;
 
try {
    $result = DB::executeProcedure('your_procedure', $bindings);
} catch (Exception $e) {
    Log::error('Procedure execution failed: ' . $e->getMessage());
    throw $e;
}

Working with Transactions

Procedures that modify data should be wrapped in transactions:

DB::transaction(function () {
    DB::executeProcedure('process_order', [
        'order_id' => $orderId,
    ]);
});

Laravel Oci8