## Getting Started

### Minimal Setup
1. **Install the package**:
   ```bash
   composer require yajra/laravel-oci8:^13

Register the service provider (Laravel < 5.5): Add to config/app.php:
```
Yajra\Oci8\Oci8ServiceProvider::class,
```
(Laravel 5.5+ auto-discovers it.)

Configure Oracle connection: Publish the config (optional but recommended):

php artisan vendor:publish --tag=oracle

Update .env with Oracle credentials:

DB_CONNECTION=oracle
DB_HOST=your_oracle_host
DB_PORT=1521
DB_SERVICE_NAME=your_service
DB_USERNAME=user
DB_PASSWORD=pass
DB_CHARSET=AL32UTF8

First use case: Query Oracle directly in a controller or model:

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

Implementation Patterns

Core Workflows

Connection Handling:
- Use DB::connection('oracle') or inject Yajra\Oci8\Oci8Connection into services.
- Supports dynamic configuration via the dynamic key in config/oracle.php:
```
'dynamic' => [App\Services\OracleConfig::class, 'getConfig'],
```

Query Building:

Leverage Laravel’s Eloquent or Query Builder with Oracle-specific syntax:

// Case-insensitive LIKE (12cR2+)
$results = DB::table('products')
    ->whereLike('name', '%search%')
    ->get();

// JSON filtering (12c02+)
$filtered = DB::table('orders')
    ->where('metadata', 'IS JSON')
    ->whereJsonContains('metadata->>\'$.status\'', '"completed"')
    ->get();

Migrations & Schema:

Use Schema::create() with Oracle-specific features:

Schema::create('posts', function (Blueprint $table) {
    $table->id(); // Uses IDENTITY for 12c+
    $table->string('title', 128); // Respects ORA_MAX_NAME_LEN
    $table->json('metadata'); // Native JSON (21c+)
    $table->comment('Stores blog posts');
});

Identity Columns: Auto-increment via IDENTITY (12c+):

$table->bigIncrements('id'); // Uses IDENTITY under the hood

Pagination & Performance:
- Optimize with cursor() for large datasets:
```
$cursor = DB::table('large_table')->cursor();
foreach ($cursor as $row) {
    // Process row-by-row
}
```
- Use retry_count and retry_delay (12c+) in .env for transient failures.

Advanced Features:

Full-Text Search (12c+):

$results = DB::table('documents')
    ->whereFullText('content', 'laravel oracle')
    ->get();

Lateral Joins (12c+):

$query = DB::table('orders')
    ->select('orders.*')
    ->joinLateral(function ($join) {
        $join->select('order_items.*')
            ->from('order_items')
            ->whereColumn('order_items.order_id', 'orders.id');
    });

Integration Tips

Eloquent Models:

Oracle’s case-sensitive collation requires explicit column definitions:

class User extends Model {
    protected $connection = 'oracle';
    protected $primaryKey = 'USER_ID'; // Oracle often uses uppercase PKs
    public $timestamps = false; // Oracle lacks default timestamp columns
}

Transactions:

Use DB::transaction() with Oracle’s SET TRANSACTION:

DB::connection('oracle')->transaction(function () {
    // Oracle-specific transaction logic
});

Schema Prefixes:
- Prefix all schema objects (e.g., PREFIX_):
```
'prefix_schema' => 'PREFIX_',
```
- Respect ORA_MAX_NAME_LEN (default: 30 chars) to avoid ORA-00972:
```
ORA_MAX_NAME_LEN=128
```
Load Balancing:
- Configure for Oracle RAC clusters:
```
DB_HOST=host1,host2,host3
DB_LOAD_BALANCE=yes
```

Debugging Queries:

Enable query logging in config/logging.php:

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

Gotchas and Tips

Pitfalls

Case Sensitivity:

Oracle collations are case-sensitive by default. Use binary_ci for case-insensitive queries (12cR2+):
```
$query->whereRaw('UPPER(name) = UPPER(?)', ['Search']);
```

Fix: Configure the Oracle user provider in config/auth.php:

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

Sequence Ownership:

Sequences must be owned by the connected user. Use Schema::table() to manage them:

Schema::table('users', function (Blueprint $table) {
    $table->sequence('user_id_seq')->ownedBySchema(true);
});

JSON Limitations:

No updates: JSON columns are read-only. Fetch, modify in PHP, and re-save:

$user->metadata = json_decode($user->metadata, true);
$user->metadata['status'] = 'updated';
$user->save();

Pagination Quirks:

Oracle’s ROWNUM behaves differently than MySQL. Use offset/limit carefully:

// Avoid:
$query->offset(100)->limit(10); // May skip rows in Oracle

// Prefer:
$query->skip(100)->take(10);

Character Sets:
- Always specify DB_CHARSET=AL32UTF8 to avoid encoding issues with special characters.
Connection Timeouts:
- Oracle connections can hang. Configure timeouts in .env:
```
DB_CONNECT_TIMEOUT=10
DB_TRANSPORT_CONNECT_TIMEOUT=60
```
Schema Builder Issues:
- Drop Table Errors: Use ifExists() to avoid ORA-00942:
```
Schema::dropIfExists('users');
```
- Index Lengths: Limit to 30 chars (Oracle 11g):
```
$table->index(['column_name'], 'idx_name_1'); // Truncates to 30 chars
```
Laravel Artisan:
- Commands like php artisan migrate may fail silently. Enable Oracle logging:
```
DB_LOG_QUERIES=true
```

Debugging Tips

Enable OCI8 Debugging: Add to php.ini or .htaccess:

oci8.log_file=/tmp/oci8.log
oci8.log_level=1023

Query Logs: Check storage/logs/oracle.log for raw SQL and execution times.

Common Errors & Fixes:

Error	Cause	Solution
`ORA-00942: table or view does not exist`	Schema prefix mismatch	Verify `prefix_schema` in config
`ORA-01031: insufficient privileges`	User lacks permissions	Grant `CREATE SESSION`, `RESOURCE` roles
`ORA-01795: maximum number of expressions in a list is 1000`	`whereIn` with >1000 values	Use `whereRaw` with `IN (SELECT ...)`
`ORA-00979: not a GROUP BY expression`	Grouping mismatch	Explicitly list all non-aggregated columns

Performance:
- Avoid SELECT *: Oracle fetches

Laravel Oci8 Laravel Package