Version

Oracle Sequence

Oracle sequences are database objects that generate sequential numbers. Laravel-OCI8 provides a convenient API for managing sequences.

Accessing the Sequence Manager

$sequence = DB::getSequence();

Available Methods

Create a Sequence

$sequence->create('seq_name');

Drop a Sequence

$sequence->drop('seq_name');

Get Next Value

$nextValue = $sequence->nextValue('seq_name');

Get Current Value

$currentValue = $sequence->currentValue('seq_name');

Get Last Insert ID

$lastId = $sequence->lastInsertId('seq_name');

Check if Sequence Exists

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

Setting Sequence Start Value

When creating a sequence, you can specify the starting value:

$sequence->create('seq_name', start: 100);

This creates a sequence that begins at 100.

Creating a Sequence with No Cache

By default, Oracle caches sequence values for performance. To disable caching:

$sequence->create('seq_name', $start = 1, $nocache = true);

When to Disable Caching

Disable caching when:

You need guaranteed sequential numbers without gaps
You're using sequences with external systems
Audit requirements demand no skipped values

Using Custom Sequences with Eloquent Models

Since version 5.2.2, you can automatically use custom sequences with Eloquent models. This is useful when tables and sequences already exist in your database.

Step 1: Define the Sequence on Your Model

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Model;
 
class User extends Model
{
    public $sequence = 'user_id_seq';
}

Step 2: Save the Model

$user = new User();
$user->name = 'John Doe';
$user->save();
 
echo $user->id; // Returns the next sequence value (e.g., 1)

When you save the model, Laravel-OCI8 automatically:

Fetches the next value from the sequence
Assigns it to the primary key
Inserts the record

Note: This feature only works with Eloquent models. When using the Query Builder, you must manually set the sequence next value.

Manual Sequence Usage with Query Builder

// Get the next sequence value
$nextId = DB::getSequence()->nextValue('user_id_seq');
 
// Use it in an insert
DB::table('users')->insert([
    'id' => $nextId,
    'name' => 'John Doe',
]);

Complete Example: Creating a User Registration System

use Illuminate\Support\Facades\DB;
 
function createUser(array $data): int
{
    $sequence = DB::getSequence();
 
    // Create sequence if it doesn't exist
    if (!$sequence->exists('users_id_seq')) {
        $sequence->create('users_id_seq', start: 1);
    }
 
    // Get next ID
    $id = $sequence->nextValue('users_id_seq');
 
    // Insert user
    DB::table('users')->insert([
        'id' => $id,
        'email' => $data['email'],
        'password' => bcrypt($data['password']),
        'created_at' => now(),
    ]);
 
    return $id;
}

Laravel Oci8