Laravel Oci8 Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require yajra/laravel-oci8:^13
   

   For Laravel 5.5+, no additional service provider registration is required. For older versions, add to config/app.php:

   Yajra\Oci8\Oci8ServiceProvider::class,
   

2. Configure .env:

   DB_CONNECTION=oracle
   DB_HOST=your_oracle_host
   DB_PORT=1521
   DB_SERVICE_NAME=your_service
   DB_DATABASE=your_db
   DB_USERNAME=your_user
   DB_PASSWORD=your_password
   

3. First Query:

   $users = DB::connection('oracle')->select('SELECT * FROM users WHERE 1=1');
   

Key First Use Cases

• Migrations: Use Laravel's schema builder with Oracle-specific syntax:

  Schema::create('users', function (Blueprint $table) {
      $table->id(); // Uses sequence in 12c+, identity in 12c+
      $table->string('name');
      $table->timestamps();
  });
  

• Query Builder: Leverage familiar Laravel syntax with Oracle optimizations:

  $results = DB::table('users')
      ->where('name', 'LIKE', '%John%')
      ->orderBy('created_at', 'desc')
      ->get();
  

• Dynamic Connections: Override config at runtime:

  $config = config('database.connections.oracle');
  $config['username'] = 'dynamic_user';
  DB::connection($config)->getPdo();
  

Implementation Patterns

Core Workflows

1. Schema Management

• Table Creation with Constraints:

  Schema::create('orders', function (Blueprint $table) {
      $table->id();
      $table->foreignId('user_id')->constrained()->onDelete('cascade');
      $table->json('metadata')->nullable(); // Oracle 21c+ native JSON
      $table->comment('Stores user orders with metadata');
  });
  

• Index Optimization:

  Schema::table('users', function (Blueprint $table) {
      $table->index('email', 'idx_users_email');
      $table->index(['name', 'created_at'], 'idx_users_name_date');
  });
  

2. Query Optimization

• Pagination:

  $users = DB::table('users')->paginate(15); // Uses Oracle-specific COUNT query
  

• JSON Queries (Oracle 12cR2+):

  $results = DB::table('products')
      ->where('metadata->>$.category', '=', 'electronics')
      ->get();
  

• Advanced Joins:

  $results = DB::table('orders')
      ->joinLateral('order_items', 'orders.id', '=', 'order_items.order_id')
      ->select('orders.*', 'order_items.product_id')
      ->get();
  

3. Connection Handling

• Multi-Database:

  // Switch connections dynamically
  $config = config('database.connections.oracle');
  $config['database'] = 'reporting_db';
  $reportData = DB::connection($config)->select('SELECT * FROM reports');
  

• Load Balancing:

  DB_HOST=host1.example.com,host2.example.com
  DB_LOAD_BALANCE=yes
  

4. Migrations & Schema

• Schema Prefixes:

  Schema::prefix('app_'); // Applies to all subsequent operations
  Schema::create('users', function (Blueprint $table) { ... });
  

• Identity Columns (12c+):

  Schema::table('posts', function (Blueprint $table) {
      $table->bigIncrements('id'); // Uses IDENTITY column
  });
  

Integration Tips

• Laravel Scout: Use with Oracle Full-Text Search (12c+):

  // Configure in config/scout.php
  'driver' => 'oracle',
  

• Eloquent: Works seamlessly with Oracle-specific types:

  class User extends Model {
      protected $connection = 'oracle';
      protected $casts = [
          'metadata' => 'json',
          'created_at' => 'datetime:Y-m-d H:i:s',
      ];
  }
  

• Artisan Commands: Extend for Oracle-specific tasks:

  php artisan db:show --connection=oracle
  

Gotchas and Tips

Common Pitfalls

1. Case Sensitivity:

   • Oracle is case-sensitive by default. Use the Oracle User Provider for auth:

     'providers' => [
         'users' => [
             'driver' => 'oracle',
             'model' => App\User::class,
         ],
     ],
     

   • For case-insensitive queries (12cR2+), use binary_ci:

     DB::table('users')->where('name', 'LIKE', '%John%', 'binary_ci');
     

2. Sequence Ownership:

   • Ensure sequences are owned by the database user:

     // Fix: Grant usage on sequences
     DB::statement('GRANT CREATE SESSION TO your_user');
     DB::statement('GRANT CREATE SEQUENCE TO your_user');
     

3. Name Length Limits:

   • Default max length is 30 chars. Increase for 12c+:

     ORA_MAX_NAME_LEN=128
     

   • Avoid errors in migrations:

     Schema::table('long_table_names', function (Blueprint $table) {
         $table->index('column_name', 'idx_ltn_column'); // Keep names < 128 chars
     });
     

4. JSON Limitations:

   • No Updates: JSON mutation requires full document replacement:

     // ❌ Avoid:
     $user->metadata->category = 'new_category';
     $user->save(); // Fails silently
     
     // ✅ Do:
     $user->metadata = json_decode($user->metadata, true);
     $user->metadata['category'] = 'new_category';
     $user->metadata = json_encode($user->metadata);
     $user->save();
     

5. Pagination Quirks:

   • Oracle 12c+ requires rownum adjustments:

     // Fix for "ORA-01795: maximum number of expressions in a list is 1000"
     DB::table('large_table')->whereIn('id', $ids)->get();
     // Use chunking:
     $ids->chunk(999)->each(function ($chunk) {
         DB::table('large_table')->whereIn('id', $chunk)->get();
     });
     

6. Connection Timeouts:

   • Configure timeouts in .env:

     DB_CONNECT_TIMEOUT=30
     DB_TRANSPORT_CONNECT_TIMEOUT=120
     

Debugging Tips

• Query Logging: Enable in config/logging.php:

  'default' => env('LOG_CHANNEL', 'oracle'),
  'channels' => [
      'oracle' => [
          'driver' => 'single',
          'path' => storage_path('logs/oracle.log'),
          'level' => 'debug',
      ],
  ],
  

• Slow Queries: Use Oracle-specific hints:

  DB::select('SELECT /*+ FIRST_ROWS(10) */ * FROM large_table WHERE active = 1');
  

• Schema Issues: Check for missing privileges:

  -- Grant required privileges
  GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO your_user;
  

Extension Points

1. Custom Grammar: Override Oracle grammar for project-specific syntax:

   class CustomOracleGrammar extends Yajra\Oci8\OracleGrammar {
       public function compileSelect(SelectQuery $query) {
           // Custom logic
           return parent::compileSelect($query);
       }
   }
   

   Register in AppServiceProvider:

   public function boot() {
       DB::connection('oracle')->setQueryGrammar(new CustomOracleGrammar());
   }
   

2. Dynamic Configuration: Extend the dynamic config array for runtime overrides:

   'oracle' => [
       'driver' => 'oracle',
       'dynamic' => [App\Services\OracleConfig::class, 'overrideConfig'],
   ],
   

   class OracleConfig {
       public static function overrideConfig(&$config) {
           $config['database'] = self::getEnvironmentDatabase();
       }
   }
   

3. PHPStan/Larastan: Enable OCI8-specific method recognition:

   includes:
       - vendor/yajra/laravel-oci8/extension.