Doctrine Oci8 Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package via Composer:

   composer require ecphp/doctrine-oci8
   

   Ensure ext-oci8 is enabled in your php.ini (extension=oci8).

2. Configuration Update your Laravel config/database.php to use the custom driver:

   'connections' => [
       'oracle' => [
           'driver'       => 'ecphp\\doctrine_oci8\\Driver',
           'host'         => env('DB_HOST', 'localhost'),
           'port'         => env('DB_PORT', '1521'),
           'database'     => env('DB_DATABASE', 'ORCL'),
           'username'     => env('DB_USERNAME', 'user'),
           'password'     => env('DB_PASSWORD', 'pass'),
           'charset'      => 'AL32UTF8',
           'prefix'       => '',
           'prefix_schema' => env('DB_SCHEMA', ''),
           'driverOptions' => [
               PDO::ATTR_EMULATE_PREPARES => false,
               PDO::ATTR_PERSISTENT       => false,
           ],
       ],
   ],
   

3. First Use Case Query an Oracle table using Eloquent or Query Builder:

   // Eloquent
   $users = DB::connection('oracle')->table('users')->get();
   
   // Raw Query
   $result = DB::connection('oracle')->select('SELECT * FROM users WHERE id = :id', ['id' => 1]);
   

Implementation Patterns

Workflows

1. Cursor Support for Large Datasets Leverage cursor-based fetching for memory efficiency:

   $stmt = DB::connection('oracle')->prepare('SELECT * FROM large_table');
   $stmt->execute();
   $cursor = $stmt->fetchAll(PDO::FETCH_ASSOC); // Use cursor mode if supported
   

2. Transaction Management Use Oracle-specific transaction features:

   DB::connection('oracle')->beginTransaction();
   try {
       DB::connection('oracle')->table('accounts')->update(['balance' => 100]);
       DB::connection('oracle')->commit();
   } catch (\Exception $e) {
       DB::connection('oracle')->rollBack();
       throw $e;
   }
   

3. Stored Procedure Calls Execute PL/SQL procedures with bind parameters:

   $result = DB::connection('oracle')->select(
       'BEGIN my_procedure(:param1, :param2); END;',
       ['param1' => 'value1', 'param2' => 'value2']
   );
   

4. Schema Management Use migrations for Oracle-specific DDL:

   Schema::connection('oracle')->create('users', function (Blueprint $table) {
       $table->bigIncrements('id');
       $table->string('name');
       $table->timestamps();
   });
   

Integration Tips

• Hybrid Applications: Combine Oracle with other databases (MySQL, PostgreSQL) in a single Laravel app.
• Query Caching: Use Laravel’s cache layer for frequent Oracle queries:

  $cached = Cache::remember('oracle_users', 60, function () {
      return DB::connection('oracle')->table('users')->get();
  });
  

• Event Listeners: Hook into illuminate.query events to log or modify Oracle queries:

  Event::listen('illuminate.query', function ($query) {
      if ($query->connectionName === 'oracle') {
          Log::debug('Oracle Query:', $query->sql);
      }
  });
  

Gotchas and Tips

Pitfalls

1. Cursor Handling

   • The package supports cursors, but ensure your Oracle client version is compatible.
   • Avoid fetching large result sets without cursors to prevent memory issues.

2. Case Sensitivity

   • Oracle table/column names are case-sensitive by default. Use double quotes for case-sensitive identifiers:

     DB::connection('oracle')->select('SELECT "UserName" FROM "Users"');
     

3. LOB Data Types

   • Large Objects (BLOB/CLOB) may require special handling. Use PDO::PARAM_LOB for bind values:

     $stmt->bindValue(':blob', $fileContent, PDO::PARAM_LOB);
     

4. Driver Options

   • Misconfigured driverOptions (e.g., PDO::ATTR_EMULATE_PREPARES) can lead to SQL injection vulnerabilities. Always disable emulated prepares:

     'driverOptions' => [PDO::ATTR_EMULATE_PREPARES => false],
     

5. Schema Migrations

   • Oracle lacks bigIncrements() support. Use bigInteger() with a trigger or sequence:

     $table->bigInteger('id')->unsigned();
     

Debugging

• Enable PDO Logging Add to config/database.php:

  'driverOptions' => [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_EMULATE_PREPARES => false,
      PDO::ATTR_STRINGIFY_FETCHES => false,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      'logging' => true, // Enable PDO logging
  ],
  

  Check Laravel logs for raw SQL and bind values.

• Oracle Errors Oracle-specific errors may not map cleanly to Laravel exceptions. Use:

  try {
      DB::connection('oracle')->select('INVALID_SQL');
  } catch (\PDOException $e) {
      Log::error('Oracle Error:', ['code' => $e->errorInfo[1], 'message' => $e->getMessage()]);
  }
  

Extension Points

1. Custom Query Builder Extensions Extend the Query Builder for Oracle-specific syntax:

   DB::connection('oracle')->extension(new class {
       public function rownum($limit) {
           return $this->getQuery()->from('(SELECT * FROM ' . $this->from . ' WHERE ROWNUM <= ' . $limit . ')');
       }
   });
   

2. Event Subscribers Create a subscriber to handle Oracle-specific events:

   Event::subscribe(new class {
       public function handleQueryExecuted(QueryExecuted $event) {
           if ($event->connectionName === 'oracle') {
               // Custom logic for Oracle queries
           }
       }
   });
   

3. Service Provider Binding Bind the custom driver in a service provider:

   public function register() {
       $this->app->bind('db.connector.ecphp\doctrine_oci8\Driver', function ($app) {
           return new \ecphp\doctrine_oci8\Driver(
               $app['config']['database.connections.oracle']
           );
       });
   }