Version

Oracle Functions

Stored functions allow you to execute business logic directly in the database and return values to your PHP application.

Prerequisites

Before running any PHP code, create the stored function in your Oracle database using SQL*Plus, SQL Developer, or a Laravel migration.

Create the Function (SQL)

CREATE OR REPLACE FUNCTION myfunc(p IN NUMBER) RETURN NUMBER AS
BEGIN
    RETURN p * 3;
END;

Calling Functions from PHP

Using the Query Builder

The simplest approach uses the Query Builder with a SELECT statement:

$result = DB::selectOne("SELECT myfunc(2) AS value FROM dual");
 
return $result->value; // Returns: 6

Using PDO with Bind Parameters

For better performance and security with complex values:

$pdo = DB::getPdo();
$x = 2;
 
$stmt = $pdo->prepare("BEGIN :y := myfunc(:x); END;");
$stmt->bindParam(':y', $y, PDO::PARAM_INT);
$stmt->bindParam(':x', $x, PDO::PARAM_INT);
$stmt->execute();
 
return $y; // Returns: 6

Using the Shortcut Method

Laravel-OCI8 provides a convenient shortcut for calling functions:

$result = DB::executeFunction(
    'function_name',          // Function name
    ['binding_1' => 'hi'],    // Input bindings
    PDO::PARAM_LOB            // Return type (optional)
);

Example: Calling myfunc

$result = DB::executeFunction('myfunc', ['p' => 3], PDO::PARAM_INT);
 
return $result; // Returns: 9

Functions with Multiple Parameters

CREATE OR REPLACE FUNCTION calculate_total(
    p_price IN NUMBER,
    p_quantity IN NUMBER
) RETURN NUMBER AS
BEGIN
    RETURN p_price * p_quantity;
END;

$result = DB::executeFunction('calculate_total', [
    'p_price' => 19.99,
    'p_quantity' => 5,
], PDO::PARAM_INT);
 
return $result; // Returns: 99.95

Functions Returning Cursors

For functions that return result sets, see the Oracle Cursors documentation.

Error Handling

Wrap function calls in try-catch blocks to handle Oracle errors gracefully:

use Illuminate\Support\Facades\DB;
use Exception;
 
try {
    $result = DB::executeFunction('myfunc', ['p' => 3]);
} catch (Exception $e) {
    Log::error('Function call failed: ' . $e->getMessage());
    throw $e;
}

Laravel Oci8