Laravel Oci8 Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require yajra/laravel-oci8:^13
   

2. Register the service provider (Laravel 5.5+): Add to config/app.php:

   Yajra\Oci8\Oci8ServiceProvider::class,
   

3. Configure .env (minimal):

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

4. Verify connection:

   php artisan db:show
   

First Use Case

Run a basic query:

use Illuminate\Support\Facades\DB;

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

Implementation Patterns

Core Workflows

1. Query Building Use Eloquent or Query Builder as usual, but with Oracle-specific syntax:

   // Eloquent
   User::where('name', 'LIKE', '%John%')->get();
   
   // Query Builder
   DB::table('users')
       ->where('created_at', '>', now()->subDays(7))
       ->orderBy('name')
       ->get();
   

2. Migrations Leverage Oracle-specific features:

   Schema::create('users', function (Blueprint $table) {
       $table->id(); // Uses sequence/identity based on Oracle version
       $table->string('name');
       $table->json('metadata'); // Oracle 12c+ JSON support
       $table->timestamps();
   });
   

3. Dynamic Configuration Override connection params at runtime:

   // config/database.php
   'oracle' => [
       'dynamic' => [App\Services\OracleConfig::class, 'getDynamicConfig'],
   ],
   

   // App\Services\OracleConfig
   public static function getDynamicConfig(&$config) {
       $config['username'] = Auth::user()->oracle_username;
   }
   

4. JSON Support (Oracle 12c+) Query JSON columns:

   User::whereJsonContains('metadata->address', ['city' => 'New York'])
       ->whereJsonLength('metadata->orders', '>', 0)
       ->get();
   

5. Pagination Use standard Laravel pagination:

   $users = User::paginate(15); // Works with Oracle-specific count queries
   

Integration Tips

• Schema Prefixes: Use DB_SCHEMA_PREFIX in .env to prefix all tables.
• Case-Sensitive Auth: Enable Oracle user provider in config/auth.php:

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

• Load Balancing: Configure multiple hosts in .env:

  DB_HOST=host1,host2,host3
  DB_LOAD_BALANCE=yes
  

Gotchas and Tips

Pitfalls

1. Case Sensitivity

   • Oracle queries are case-sensitive by default. Use BINARY_CI collation for case-insensitive LIKE:

     DB::table('users')->where('name', 'LIKE', '%john%')->get();
     

   • Enable the Oracle user provider to handle case-sensitive auth issues.

2. Sequence Ownership

   • Sequences must be owned by the database user. Use prefix_schema in config if needed:

     DB_PREFIX_SCHEMA=your_schema
     

3. JSON Limitations

   • Only read-only JSON operations are supported. For updates, modify the JSON in PHP and save back.

4. Pagination Quirks

   • Oracle 12c+ uses ROWNUM for pagination. For older versions, ensure DB_SERVER_VERSION is set correctly.

5. Max Name Length

   • Default is 30 chars. Increase with:

     ORA_MAX_NAME_LEN=128
     

   • Requires Oracle 12c02+.

6. Trigger Prefixes

   • If using schema prefixes, ensure triggers are dropped correctly:

     Schema::dropIfExists('your_schema_your_table');
     

Debugging Tips

• Enable Query Logging:

  DB::connection('oracle')->enableQueryLog();
  $results = DB::select('SELECT * FROM users');
  dd(DB::getQueryLog());
  

• Check Oracle Version:

  $version = DB::connection('oracle')->getPdo()->getAttribute(\PDO::ATTR_SERVER_VERSION);
  

• Common Errors:
  • ORA-00942: Missing table prefix. Verify DB_PREFIX and DB_PREFIX_SCHEMA.
  • ORA-01031: Authentication failed. Double-check credentials in .env.
  • ORA-00904: Invalid column name. Ensure column names match Oracle’s case sensitivity.

Extension Points

1. Custom Grammar Extend Yajra\Oci8\OracleGrammar for Oracle-specific syntax:

   class CustomOracleGrammar extends OracleGrammar {
       public function compileWhereJsonContains($expression) {
           // Custom JSON logic
       }
   }
   

   Register in AppServiceProvider:

   DB::resolverFor('oracle', function ($connection, $configuration, $name, $query) {
       $grammar = new CustomOracleGrammar();
       return new OracleConnection($connection, $configuration, $query, $grammar);
   });
   

2. PHPStan/Larastan Include the extension for static analysis:

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

3. Dynamic Configuration Use the dynamic callback to override connection params per request:

   public static function dynamicConfig(&$config) {
       if (request()->has('tenant')) {
           $config['database'] = "tenant_{$tenant}";
       }
   }
   

4. Full-Text Search Leverage Oracle’s CONTAINS for full-text queries:

   DB::table('articles')
       ->whereRaw('CONTAINS(content, :search, 1) > 0', ['search' => 'laravel oracle'])
       ->get();