Getting Started

Minimal Steps

Install the Bundle Run:
```
composer require ecphp/doctrine-oci8-bundle
```
This installs both the bundle and its dependency ecphp/doctrine-oci8.

Configure Doctrine for Oracle Ensure your config/packages/doctrine.yaml includes the OCI8 driver. Example:

doctrine:
    dbal:
        driver: 'oci8'
        url: '%env(DATABASE_URL)%'
        server_version: '12c' # Adjust to your Oracle version

Set Up Oracle Extension Ensure the ext-oci8 PHP extension is installed and enabled in your php.ini:
```
extension=oci8
```
Environment Variables Configure your .env file with Oracle connection details:
```
DATABASE_URL=oci8://username:password@host:port/service_name
```

Verify Connection Run a test query via Tinker or a controller:

use Doctrine\DBAL\Connection;

$connection = \Doctrine\DBAL\DriverManager::getConnection([
    'url' => 'oci8://username:password@host:port/service_name',
]);
$result = $connection->executeQuery('SELECT * FROM v$version')->fetchAll();

First Use Case

Replace a MySQL/PostgreSQL-based Laravel application with Oracle support for a legacy system migration. Use Doctrine’s ORM for entity mapping and leverage OCI8’s native Oracle features (e.g., PL/SQL blocks, LOBs).

Implementation Patterns

Workflows

Entity Mapping Define Doctrine entities with Oracle-specific types (e.g., BLOB, CLOB, TIMESTAMP WITH TIME ZONE):

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class User {
    #[ORM\Id, ORM\Column(type: 'integer')]
    private $id;

    #[ORM\Column(type: 'string')]
    private $name;

    #[ORM\Column(type: 'blob')] // Oracle BLOB type
    private $profilePicture;
}

Querying with DQL Use Doctrine Query Language (DQL) for complex queries:

$query = $entityManager->createQuery(
    'SELECT u FROM App\Entity\User u WHERE u.name LIKE :name'
)->setParameter('name', '%John%');
$users = $query->getResult();

Transactions Manage transactions explicitly for Oracle-specific operations:

$entityManager->beginTransaction();
try {
    $entityManager->persist($user);
    $entityManager->flush();
    $entityManager->commit();
} catch (\Exception $e) {
    $entityManager->rollback();
    throw $e;
}

Stored Procedures Call Oracle PL/SQL procedures via Doctrine:

$stmt = $connection->prepare('BEGIN your_package.your_procedure(:param1, :param2); END;');
$stmt->bindValue('param1', $value1);
$stmt->bindValue('param2', $value2);
$stmt->execute();

Migrations Use Doctrine Migrations for schema changes:

php bin/console doctrine:migrations:diff
php bin/console doctrine:migrations:migrate

Integration Tips

Laravel Eloquent + Doctrine: Use doctrine/orm alongside Laravel’s Eloquent by configuring multiple Doctrine connections in doctrine.yaml.
Caching: Leverage Oracle’s advanced caching features (e.g., result caching) via Doctrine’s second-level cache.
Logging: Enable SQL logging in doctrine.yaml for debugging:
```
dbal:
    logging: true
```
Testing: Use Dockerized Oracle instances (e.g., gvenzl/oracle-xe) for CI/CD pipelines.

Gotchas and Tips

Pitfalls

Oracle Version Compatibility
- Ensure the server_version in doctrine.yaml matches your Oracle DB version (e.g., 12c, 19c). Mismatches may cause SQL syntax errors.
- Fix: Check supported versions in Doctrine OCI8 docs.
Character Encoding
- Oracle uses AL32UTF8 by default, but PHP’s oci8 may default to UTF8. Configure encoding in DATABASE_URL:
```
DATABASE_URL=oci8://user:pass@host:port/service_name?charset=AL32UTF8
```
- Fix: Add charset=AL32UTF8 to the connection URL or set it in doctrine.yaml:
```
dbal:
    charset: AL32UTF8
```
Case Sensitivity
- Oracle identifiers (tables/columns) are case-sensitive if quoted (e.g., "User" vs user). Doctrine entities must match the exact case.
- Fix: Use quoteIdentifier in DQL or configure Doctrine to handle case sensitivity:
```
$query->setHint('case_sensitive', true);
```
LOB Handling
- Large Objects (BLOB/CLOB) may require temporary tables or streaming in PHP. Avoid loading entire LOBs into memory.
- Fix: Use Doctrine’s StreamableFile or chunked reads/writes.
Connection Pooling
- OCI8 does not natively support connection pooling. Use a connection pooler like pdo_pgsql’s pgbouncer equivalent for Oracle (e.g., Oracle’s UCP).
- Fix: Configure a pooler in your deployment stack (e.g., Docker with oracle/ucp).
Time Zones
- Oracle’s TIMESTAMP WITH TIME ZONE may not map cleanly to PHP’s DateTime. Use DateTimeImmutable and specify time zones explicitly.
- Fix: Configure Doctrine’s datetime_type:
```
dbal:
    datetime_type: 'datetime_immutable'
```

Debugging

Enable SQL Logging: Add to doctrine.yaml:

dbal:
    logging: true
    logging_format: '%%timestamp%% %%sql%% %%params%%'

Check Oracle Errors: OCI8 throws PDOException with Oracle-specific error codes. Log these for troubleshooting:

try {
    $result = $connection->executeQuery('SELECT * FROM non_existent_table');
} catch (\PDOException $e) {
    error_log('Oracle Error: ' . $e->getCode() . ' - ' . $e->getMessage());
}

Use oci8.new_error_mode: Set in php.ini to get OCI8 errors in a more debug-friendly format:
```
oci8.new_error_mode = 1
```

Configuration Quirks

Default Connection: The bundle assumes a single Doctrine connection. For multi-connection setups, explicitly configure the OCI8 connection in doctrine.yaml:

doctrine:
    dbal:
        connections:
            oracle:
                driver: 'oci8'
                url: '%env(DATABASE_URL)%'
                server_version: '12c'

Environment Variables: The bundle does not override Laravel’s .env parsing. Ensure DATABASE_URL is properly formatted for OCI8:
```
DATABASE_URL=oci8://user:pass@//host:port/service_name
```
(Note the double // for Oracle’s easy connect syntax.)

Extension Points

Custom Drivers Extend ecphp/doctrine-oci8 to add Oracle-specific features (e.g., custom types for RAW, INTERVAL):
```
use Doctrine\DBAL\Types\Type;

Type::addType('oracle_raw', OracleRawType::class);
```

Event Subscribers Hook into Doctrine lifecycle events for Oracle-specific logic (e.g., auditing):

use Doctrine\Common\EventSubscriber;

class OracleAuditSubscriber implements EventSubscriber {
    public function getSubscribedEvents() {
        return ['prePersist', 'preUpdate'];
    }

    public function prePersist(LifecycleEventArgs $args) {
        $entity = $args->getEntity();
        // Add Oracle-specific audit fields
    }
}

Query AST Modifiers Use Doctrine’s Query Language AST to transform DQL for Oracle (e.g., converting LIMIT/OFFSET to Oracle’s ROWNUM):

use Doctrine\ORM\Query\AST;

class OracleLimitOffsetWalker extends AST\AbstractSqlWalker {
    public function walkLimitOffsetClause(AST\LimitOffsetClause $clause) {
        // Convert LIMIT/OFFSET to Oracle syntax
    }
}

Doctrine Oci8 Bundle Laravel Package