Laravel OCI8 (yajra/laravel-oci8)

Laravel OCI8

https://github.com/yajra/laravel-oci8
Admin
Laravel OCI8 is an Oracle Database Driver package for Laravel that extends Illuminate/Database using...

Tokens:11,976
Snippets:96
Trust Score:9.7
Update:2 weeks ago
Show doc for...
Context Summary (auto-generated)
Raw
# Laravel OCI8

Laravel OCI8 is an Oracle Database Driver package for Laravel that extends Illuminate/Database to communicate with Oracle databases using the OCI8 PHP extension. It provides seamless integration with Laravel's database layer, enabling developers to use Eloquent ORM, Query Builder, Schema Builder, and Migrations with Oracle databases while maintaining full compatibility with Laravel's database API.

The package supports Oracle 11g through 12c+ features including sequences, triggers, stored procedures, functions, cursors, BLOB/CLOB handling, and JSON queries. It handles Oracle-specific quirks like case-sensitive queries, the 1000-item WHERE IN clause limitation, and automatic management of auto-increment fields through sequences and triggers.

## Installation

Install the package via Composer and configure the Oracle database connection.

```bash
composer require yajra/laravel-oci8:^13
```

## Database Configuration

Configure the Oracle connection in your Laravel application's database configuration.

```php
// config/database.php
'oracle' => [
    'driver'         => 'oracle',
    'tns'            => env('DB_TNS', ''),
    'host'           => env('DB_HOST', ''),
    'port'           => env('DB_PORT', '1521'),
    'database'       => env('DB_DATABASE', ''),
    'service_name'   => env('DB_SERVICE_NAME', ''),
    'username'       => env('DB_USERNAME', ''),
    'password'       => env('DB_PASSWORD', ''),
    'charset'        => env('DB_CHARSET', 'AL32UTF8'),
    'prefix'         => env('DB_PREFIX', ''),
    'prefix_schema'  => env('DB_SCHEMA_PREFIX', ''),
    'edition'        => env('DB_EDITION', 'ora$base'),
    'server_version' => env('DB_SERVER_VERSION', '11g'),
    'load_balance'   => env('DB_LOAD_BALANCE', 'yes'),
    'max_name_len'   => env('ORA_MAX_NAME_LEN', 30),
    'dynamic'        => [],
    'sessionVars'    => [
        'NLS_TIME_FORMAT'         => 'HH24:MI:SS',
        'NLS_DATE_FORMAT'         => 'YYYY-MM-DD HH24:MI:SS',
        'NLS_TIMESTAMP_FORMAT'    => 'YYYY-MM-DD HH24:MI:SS',
        'NLS_TIMESTAMP_TZ_FORMAT' => 'YYYY-MM-DD HH24:MI:SS TZH:TZM',
        'NLS_NUMERIC_CHARACTERS'  => '.,',
    ],
],
```

```ini
# .env
DB_CONNECTION=oracle
DB_HOST=oracle.host
DB_PORT=1521
DB_SERVICE_NAME=orcl
DB_DATABASE=xe
DB_USERNAME=hr
DB_PASSWORD=hr
```

## Multi-Host Cluster Configuration

Connect to Oracle RAC clusters with multiple hosts for load balancing and failover.

```ini
# .env for cluster connection
DB_CONNECTION=oracle
DB_HOST=oracle1.host, oracle2.host
DB_PORT=1521
DB_SERVICE_NAME=orcl
DB_LOAD_BALANCE=yes
DB_DATABASE=xe
DB_USERNAME=hr
DB_PASSWORD=hr
```

## Dynamic Configuration

Set database credentials dynamically at runtime using a callback function.

```php
// config/database.php
'oracle' => [
    'driver' => 'oracle',
    'host' => 'oracle.host',
    'port' => '1521',
    'service_name' => 'sid_alias',
    'prefix' => 'schemaowner',
    'dynamic' => [App\Models\Oracle\Config::class, 'dynamicConfig'],
]

// App\Models\Oracle\Config.php
namespace App\Models\Oracle;

use Illuminate\Support\Facades\Auth;

class Config
{
    public static function dynamicConfig(&$config)
    {
        if (Auth::check()) {
            $config['username'] = App\Oracle\Config::getOraUser();
            $config['password'] = App\Oracle\Config::getOraPass();
        }
    }
}
```

## Schema Builder - Creating Tables with Auto-Increment

Create tables with automatic sequence and trigger generation for auto-incrementing primary keys.

```php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::create('posts', function (Blueprint $table) {
    $table->increments('id');
    $table->string('title');
    $table->string('slug');
    $table->text('content');
    $table->timestamps();
});

// This automatically creates:
// - posts (table)
// - posts_id_seq (sequence)
// - posts_id_trg (trigger)

// With custom start value and no cache
Schema::create('orders', function (Blueprint $table) {
    $table->increments('id')->start(10000)->nocache();
    $table->string('order_number');
    $table->decimal('total', 10, 2);
    $table->timestamps();
});
```

## Query Builder - Basic Operations

Use Laravel's Query Builder with Oracle databases for standard CRUD operations.

```php
use Illuminate\Support\Facades\DB;

// Select records
$users = DB::table('users')->where('status', 'active')->get();

// Insert and get ID
$id = DB::connection('oracle')->table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0],
    'userid' // specify the auto-incrementing column name
);

// Update records
DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 100]);

// Delete records
DB::table('users')->where('votes', '<', 10)->delete();
```

## Sequence Management

Create, manage, and query Oracle sequences programmatically.

```php
use Illuminate\Support\Facades\DB;

$sequence = DB::getSequence();

// Create a sequence
$sequence->create('user_id_seq');

// Create with options: start value, nocache, min, max, increment
$sequence->create('order_seq', 1000, true, 1, 999999, 1);

// Get next value
$nextId = $sequence->nextValue('user_id_seq');

// Get current value
$currentId = $sequence->currentValue('user_id_seq');
$lastId = $sequence->lastInsertId('user_id_seq');

// Check if sequence exists
if ($sequence->exists('user_id_seq')) {
    // Sequence exists
}

// Drop a sequence
$sequence->drop('user_id_seq');
```

## Trigger Management

Create and manage Oracle triggers, including auto-increment triggers.

```php
use Illuminate\Support\Facades\DB;

$trigger = DB::getTrigger();

// Create an auto-increment trigger
$trigger->autoIncrement(
    'users',           // table name
    'id',              // column name
    'users_id_trg',    // trigger name
    'users_id_seq'     // sequence name
);

// Drop a trigger
$trigger->drop('users_id_trg');
```

## OracleEloquent Model with Custom Sequence

Use custom sequences with Eloquent models for auto-incrementing primary keys.

```php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    public $sequence = 'user_id_seq';

    protected $table = 'users';
}

// Usage
$user = new User();
$user->name = 'John Doe';
$user->email = 'john@example.com';
$user->save();

echo $user->id; // Auto-assigned from sequence

// Get next sequence value directly
$nextId = User::nextValue();
// Or with specific sequence
$nextId = User::nextValue('custom_seq');
```

## BLOB Field Handling with OracleEloquent

Handle binary/BLOB fields directly in Eloquent models.

```php
namespace App\Models;

use Yajra\Oci8\Eloquent\OracleEloquent;

class Document extends OracleEloquent
{
    protected $table = 'documents';

    // Define binary/blob fields
    protected $binaries = ['file_content', 'thumbnail'];

    // Optional: custom sequence name
    protected $sequence = 'documents_id_seq';
}

// Insert with BLOB
$doc = new Document();
$doc->name = 'Annual Report';
$doc->file_content = file_get_contents('/path/to/report.pdf');
$doc->thumbnail = file_get_contents('/path/to/thumb.png');
$doc->save();

// Update BLOB field
$doc = Document::find(1);
$doc->file_content = file_get_contents('/path/to/updated-report.pdf');
$doc->save();
```

## Query Builder BLOB Operations

Insert and update BLOB fields using the Query Builder.

```php
use Illuminate\Support\Facades\DB;

// Insert with BLOB and get auto-increment ID
$id = DB::table('documents')->insertLob(
    ['name' => 'Contract Document', 'created_at' => now()],  // regular fields
    ['content' => file_get_contents('/path/to/contract.pdf')], // blob fields
    'id' // sequence column (defaults to 'id')
);

// Update with BLOB
DB::table('documents')->where('id', 1)->updateLob(
    ['name' => 'Updated Contract', 'updated_at' => now()],   // regular fields
    ['content' => file_get_contents('/path/to/new-contract.pdf')] // blob fields
);

// Insert BLOB via transaction (manual method)
DB::transaction(function ($conn) {
    $pdo = $conn->getPdo();
    $sql = "INSERT INTO documents (id, content)
            VALUES (documents_id_seq.nextval, EMPTY_BLOB())
            RETURNING content INTO :blob";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':blob', $lob, PDO::PARAM_LOB);
    $stmt->execute();
    $lob->save(file_get_contents('/path/to/file.pdf'));
});
```

## Stored Procedure Execution

Execute Oracle stored procedures with input and output parameters.

```sql
-- Create procedure in Oracle
CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_employee_id IN NUMBER,
    p_bonus OUT NUMBER
) AS
BEGIN
    SELECT salary * 0.1 INTO p_bonus
    FROM employees
    WHERE employee_id = p_employee_id;
END;
```

```php
use Illuminate\Support\Facades\DB;
use PDO;

// Simple procedure execution
$result = DB::executeProcedure('calculate_bonus', [
    'p_employee_id' => 100,
]);

// Manual execution with output parameters
$pdo = DB::getPdo();
$employeeId = 100;

$stmt = $pdo->prepare("begin calculate_bonus(:p_employee_id, :p_bonus); end;");
$stmt->bindParam(':p_employee_id', $employeeId, PDO::PARAM_INT);
$stmt->bindParam(':p_bonus', $bonus, PDO::PARAM_INT);
$stmt->execute();

echo "Bonus: $bonus"; // Output parameter value

// Procedure with complex bindings
$result = DB::executeProcedure('process_order', [
    'order_id' => [
        'value' => 12345,
        'type' => PDO::PARAM_INT,
    ],
    'status' => [
        'value' => 'COMPLETED',
        'type' => PDO::PARAM_STR,
        'length' => 50,
    ],
]);
```

## Stored Procedure with Cursor

Execute procedures that return result sets via REF CURSOR.

```sql
-- Create procedure returning cursor
CREATE OR REPLACE PROCEDURE get_employees_by_dept(
    p_dept_id IN NUMBER,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
END;
```

```php
use Illuminate\Support\Facades\DB;

// Execute procedure with cursor
$employees = DB::executeProcedureWithCursor(
    'get_employees_by_dept',
    ['p_dept_id' => 10],
    ':p_cursor' // cursor parameter name
);

foreach ($employees as $employee) {
    echo "{$employee->first_name} {$employee->last_name}: {$employee->salary}\n";
}

// Manual cursor handling for complex scenarios
$sql = "begin get_employee_report(:dept_id, :cursor, :status, :message); end;";

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

    $deptId = 20;
    $stmt->bindParam(':dept_id', $deptId, PDO::PARAM_INT);
    $stmt->bindParam(':cursor', $cursor, PDO::PARAM_STMT);
    $stmt->bindParam(':status', $status, PDO::PARAM_INT);
    $stmt->bindParam(':message', $message, PDO::PARAM_STR, 200);
    $stmt->execute();

    oci_execute($cursor, OCI_DEFAULT);
    oci_fetch_all($cursor, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
    oci_free_cursor($cursor);

    return $data;
});
```

## Oracle Function Execution

Execute Oracle functions and retrieve return values.

```sql
-- Create function in Oracle
CREATE OR REPLACE FUNCTION calculate_tax(p_amount IN NUMBER) RETURN NUMBER AS
BEGIN
    RETURN p_amount * 0.08;
END;
```

```php
use Illuminate\Support\Facades\DB;
use PDO;

// Via Query Builder
$result = DB::selectOne("SELECT calculate_tax(1000) as tax FROM dual");
echo "Tax: {$result->tax}"; // 80

// Using executeFunction helper
$tax = DB::executeFunction(
    'calculate_tax(:amount)',
    [':amount' => 1000],
    PDO::PARAM_STR // return type
);
echo "Tax: $tax";

// For LOB return values
$content = DB::executeFunction(
    'get_document_content(:doc_id)',
    [':doc_id' => 123],
    PDO::PARAM_LOB,
    4000 // max length
);

// Manual execution via PDO
$pdo = DB::getPdo();
$amount = 1000;

$stmt = $pdo->prepare("begin :result := calculate_tax(:amount); end;");
$stmt->bindParam(':result', $result);
$stmt->bindParam(':amount', $amount);
$stmt->execute();

echo "Tax: $result";
```

## Oracle Cursor Function

Query data using functions that return REF CURSOR.

```sql
-- Create cursor function
CREATE OR REPLACE FUNCTION get_cities(p_limit IN NUMBER) RETURN SYS_REFCURSOR AS
    rc SYS_REFCURSOR;
BEGIN
    OPEN rc FOR SELECT city, country FROM locations WHERE ROWNUM < p_limit;
    RETURN rc;
END;
```

```php
use Illuminate\Support\Facades\DB;
use PDO;

// Via Query Builder
$cities = DB::select("SELECT get_cities(10) AS cities FROM dual");

// Via PDO for full cursor data
$pdo = DB::getPdo();
$stmt = $pdo->prepare("SELECT get_cities(10) AS cities FROM dual");
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach ($results as $row) {
    echo "{$row->city}, {$row->country}\n";
}
```

## Session Variables and Date Formatting

Configure Oracle session variables including date/time formats.

```php
use Illuminate\Support\Facades\DB;

// Set date format for the session
DB::setDateFormat('MM/DD/YYYY');

// Set multiple session variables
DB::connection('oracle')->setSessionVars([
    'NLS_DATE_FORMAT'      => 'YYYY-MM-DD',
    'NLS_TIMESTAMP_FORMAT' => 'YYYY-MM-DD HH24:MI:SS.FF',
    'NLS_NUMERIC_CHARACTERS' => '.,',
    'NLS_TERRITORY'        => 'AMERICA',
]);

// Set current schema
DB::connection('oracle')->setSchema('HR');

// Enable case-insensitive search and sort
DB::connection('oracle')->useCaseInsensitiveSession();

// Revert to case-sensitive (default)
DB::connection('oracle')->useCaseSensitiveSession();
```

## Oracle User Provider for Authentication

Configure case-insensitive user authentication for Oracle databases.

```php
// config/auth.php
'providers' => [
    'users' => [
        'driver' => 'oracle',  // Use Oracle user provider
        'model' => App\Models\User::class,
    ],
],

// The Oracle user provider automatically handles case-insensitive
// credential matching using UPPER() for username/email lookups
// This prevents authentication failures due to Oracle's default
// case-sensitive string comparisons
```

## WHERE IN Clause Handling

The package automatically handles Oracle's 1000-item limit in WHERE IN clauses.

```php
use Illuminate\Support\Facades\DB;

// Arrays with more than 1000 items are automatically chunked
$userIds = range(1, 2500); // 2500 IDs

// This is automatically split into multiple OR conditions
$users = DB::table('users')
    ->whereIn('id', $userIds)
    ->get();

// Equivalent to:
// WHERE (id IN (1..1000)) OR (id IN (1001..2000)) OR (id IN (2001..2500))
```

## Standalone Usage (Outside Laravel)

Use Laravel OCI8 without the full Laravel framework.

```php
require 'vendor/autoload.php';

use Illuminate\Database\Capsule\Manager as Capsule;
use Illuminate\Events\Dispatcher;
use Illuminate\Container\Container;
use Yajra\Oci8\Connectors\OracleConnector;
use Yajra\Oci8\Oci8Connection;

$capsule = new Capsule;

// Register Oracle driver
$manager = $capsule->getDatabaseManager();
$manager->extend('oracle', function ($config) {
    $connector = new OracleConnector();
    $connection = $connector->connect($config);
    $db = new Oci8Connection($connection, $config["database"], $config["prefix"]);

    // Set session variables
    $db->setSessionVars([
        'NLS_TIME_FORMAT'         => 'HH24:MI:SS',
        'NLS_DATE_FORMAT'         => 'YYYY-MM-DD HH24:MI:SS',
        'NLS_TIMESTAMP_FORMAT'    => 'YYYY-MM-DD HH24:MI:SS',
        'NLS_TIMESTAMP_TZ_FORMAT' => 'YYYY-MM-DD HH24:MI:SS TZH:TZM',
    ]);

    return $db;
});

// Add connection
$capsule->addConnection([
    'driver'   => 'oracle',
    'host'     => 'oracle.host',
    'database' => 'xe',
    'username' => 'user',
    'password' => 'password',
    'prefix'   => '',
    'port'     => 1521
]);

// Optional: Set event dispatcher
$capsule->setEventDispatcher(new Dispatcher(new Container));

// Make globally available
$capsule->setAsGlobal();

// Boot Eloquent
$capsule->bootEloquent();

// Define model
class User extends Illuminate\Database\Eloquent\Model {
    public $timestamps = false;
}

// Query data
$user = User::find(1);
echo $user->toJson();
```

## JSON Support (Oracle 12c+)

Query JSON columns in Oracle 12c and later (read-only support).

```php
use Illuminate\Support\Facades\DB;

// Query JSON data
$users = DB::table('users')
    ->whereJsonContains('preferences->notifications', 'email')
    ->get();

$orders = DB::table('orders')
    ->where('metadata->status', 'completed')
    ->get();

// Note: JSON mutation/update operations are not supported
// For updates, modify the full JSON document in PHP and save it back
$user = DB::table('users')->where('id', 1)->first();
$preferences = json_decode($user->preferences, true);
$preferences['theme'] = 'dark';
DB::table('users')
    ->where('id', 1)
    ->update(['preferences' => json_encode($preferences)]);
```

## Creating Procedures via Migrations

Create and manage stored procedures through Laravel migrations.

```php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class CreateCalculateTotalProcedure extends Migration
{
    public function up()
    {
        $sql = "
            CREATE OR REPLACE PROCEDURE calculate_order_total(
                p_order_id IN NUMBER,
                p_total OUT NUMBER
            ) AS
            BEGIN
                SELECT SUM(quantity * unit_price)
                INTO p_total
                FROM order_items
                WHERE order_id = p_order_id;
            END;
        ";

        DB::connection()->getPdo()->exec($sql);
    }

    public function down()
    {
        DB::connection()->getPdo()->exec("DROP PROCEDURE calculate_order_total");
    }
}
```

## Summary

Laravel OCI8 serves as a comprehensive bridge between Laravel and Oracle databases, making it ideal for enterprise applications that require Oracle's robust features while maintaining Laravel's elegant syntax. Common use cases include migrating legacy Oracle systems to modern Laravel applications, building enterprise applications with Oracle backends, integrating with existing Oracle stored procedures and functions, and managing complex BLOB/CLOB data types in document management systems.

The package integrates seamlessly with Laravel's ecosystem by extending the core database components rather than replacing them. This means existing Laravel knowledge transfers directly - use the same Query Builder syntax, Schema Builder commands, and Eloquent patterns you already know. For Oracle-specific features like sequences, triggers, and stored procedures, the package provides intuitive APIs that feel natural within the Laravel framework while respecting Oracle's conventions and capabilities.