Oracledb Laravel Package

Getting Started

1. Install the extension before the package. The driver does not use pdo_oci; it depends on yajra/laravel-pdo-via-oci8 which wraps the PHP oci8 extension. You need:

• Oracle Instant Client (basic + sdk) on the host
• oci8 compiled against it: pecl install oci8 (or docker-php-ext-configure oci8 --with-oci8=instantclient,/opt/oracle/instantclient)

If php -m | grep oci8 is empty, nothing below will work — the connector will throw could not find driver or undefined function oci_connect.

composer require jfelder/oracledb

2. Match the driver major to your Laravel major. As of v13.0.0 the package tracks Laravel's own branch strategy:

v13 is a breaking release for a reason that has nothing to do with the Oracle grammar — it drops support below PHP 8.3 and re-targets master at Laravel 13. If you are still on Laravel 12, pin ^12.1 and ignore v13; conversely do not try to upgrade to v13 on PHP 8.2, Composer will refuse. Confirm your floor before you start:

php -v        # needs 8.3.x or newer for v13

Laravel ≥5.5 auto-discovers Jfelder\OracleDB\OracleDBServiceProvider. On older versions add it to config/app.php providers manually.

3. Add the connection. You can php artisan vendor:publish --tag=oracledb for a config stub, but most teams just drop the block into config/database.php:

'oracle' => [
    'driver'       => 'oracle',
    'host'         => env('DB_HOST'),
    'port'         => env('DB_PORT', '1521'),
    'database'     => env('DB_DATABASE'),      // SID
    'service_name' => env('DB_SERVICE_NAME'),  // SERVICE_NAME (preferred)
    'username'     => env('DB_USERNAME'),
    'password'     => env('DB_PASSWORD'),
    'charset'      => env('DB_CHARSET', 'AL32UTF8'),
    'prefix'       => '',
],

Pick one of database (SID) or service_name — not both. Modern Oracle setups almost always want service_name.

4. Smoke test. This is the first query every dev should run — it exercises the connector, grammar, and PDO wrapper in one shot:

DB::connection('oracle')->select('select 1 as ok from dual');
DB::connection('oracle')->table('user_tables')->limit(5)->get();

Where to look first in the source:

You will spend most debugging time in those last two files.

Implementation Patterns

Keep Oracle on its own connection. In mixed apps (MySQL/Postgres app DB + legacy Oracle), treat Oracle as a secondary connection and never let DB::connection() default to it. Resolver, grammar, and type mapping all diverge from MySQL expectations, so isolate:

$oracle = DB::connection('oracle');
$oracle->beginTransaction();
try {
    $oracle->table('ORDERS')->insert([...]);
    $oracle->commit();
} catch (Throwable $e) {
    $oracle->rollBack();
    throw $e;
}

Model conventions for Oracle tables.

class Order extends Model
{
    protected $connection = 'oracle';
    protected $table      = 'ORDERS';      // uppercase — Oracle folds unquoted idents
    protected $primaryKey = 'ORDER_ID';
    public $incrementing  = false;        // handled by sequence + trigger
    protected $keyType    = 'int';
    public $timestamps    = false;        // only if the table has no created_at/updated_at
}

insertGetId on this driver works by looking up the sequence named <table>_<pk>_seq and calling nextval. If your sequence is named differently, either use DB::raw or override:

DB::connection('oracle')->table('ORDERS')->insertGetId($row, 'ORDER_ID');
// or explicitly
$id = DB::connection('oracle')->selectOne("select ORDERS_SEQ.nextval as id from dual")->id;

Migrations — the grammar maps types but not everything.

• string() → VARCHAR2(n)
• text() → CLOB
• integer() → NUMBER(10)
• boolean() → NUMBER(1) (still returns 0/1, not bool unless you cast)
• increments()/bigIncrements() → creates table + sequence + trigger

Anything Oracle-specific (partitions, tablespaces, RAW, TIMESTAMP WITH TIME ZONE) belongs in DB::statement() in a migration, not the schema builder:

Schema::create('EVENTS', function (Blueprint $t) {
    $t->bigIncrements('ID');
    $t->string('NAME', 200);
    $t->timestamp('CREATED_AT')->useCurrent();
});

DB::connection('oracle')->statement(
    "alter table \"EVENTS\" add constraint EVENTS_PK primary key (\"ID\")"
);

Pagination on pre-12c databases. The grammar rewrites limit/offset into ROWNUM subqueries. That works but the count(*) wrapper is expensive on large tables — cache the total or use a cursor-based pagination with ORDER BY ... WHERE id > :last.

Stored procedures / functions. The PDO wrapper supports OCI8 calls but not through the normal builder. Reach for the raw PDO handle:

$pdo = DB::connection('oracle')->getPdo();
$stmt = $pdo->prepare("begin :out := MY_PKG.CALC(:in); end;");
$stmt->bindParam(':out', $out, PDO::PARAM_STR, 200);
$stmt->bindParam(':in',  $in,  PDO::PARAM_STR);
$stmt->execute();

Read-heavy reporting paths — wrap big queries in a Repository/Query Object so the raw SQL and bindings live together and are unit-testable:

final class MonthlyOrders
{
    public function __construct(private Connection $oracle) {}

    public function for(int $year): array
    {
        return $this->oracle->select(
            "select trunc(ORDER_DATE,'MM') as mon, count(*) as n
               from ORDERS
              where extract(year from ORDER_DATE) = :y
              group by trunc(ORDER_DATE,'MM')", ['y' => $year]
        );
    }
}

Testing. SQLite in-memory will not accept any Oracle grammar output. Use gvenzl/oracle-xe or container-registry.oracle.com/database/free in CI, and mark Oracle-touching tests with a group so the default suite stays fast. RefreshDatabase works because the grammar understands drop table ... cascade constraints, but it will not clean up sequences — add explicit drop sequence if exists in a tearDown.

When you move CI to PHP 8.3 for v13, also bump the shivammathur/setup-php matrix and check the oci8 build against the new PHP minor — the extension is picky about API changes and pecl install oci8 will sometimes fail until the PECL release catches up. Installing via --with-oci8=instantclient,... from source against the current PHP dev headers is more reliable in that window.

Gotchas and Tips

Identifiers are case-sensitive and get uppercase-quoted. Laravel's grammar quotes everything with ", e.g. "orders". Oracle then looks for a lowercase table — which doesn't exist. Always use the exact case Oracle stores (usually uppercase). If your schema was created with quoted lowercase names, you must keep lowercase everywhere, consistently.

lastInsertId is a lie on 12c-less databases (and usually still on 12c+). Don't rely on $model->save() populating id on Oracle; either use insertGetId() or fetch sequence.currval immediately after. And remember currval is session-scoped — safe, but not available from a different connection pool.

service_name vs database. If you see ORA-12505: TNS:listener does not currently know of SID you've put the SID in the service_name slot (or vice versa). Modern Oracle (ORCLPDB1, XEPDB1, FREE) uses service names; classic setups use SIDs. The connector builds the DSN differently for each.

NLS configuration. The connector honours charset but not NLS_DATE_FORMAT. If you insert '2024-01-15 10:00:00' as a string and get ORA-01861: literal does not match format string, you need either a bind with DateTime::class (the grammar handles it) or a raw TO_DATE(..., 'YYYY-MM-DD HH24:MI:SS'). Some teams set alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' in a per-connection listener via events:

DB::connection('oracle')->getPdo()
    ->exec("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'");

Do this in a service provider or a DB::listen hook that runs once per connection — not per query.

No bool type. where('flag', true) becomes = 1 fine, but selecting a NUMBER(1) returns '1'/'0' strings from PDO. Add casts.

String functions differ. LIKE is case-sensitive (LOWER(x) LIKE LOWER(?) needed), concat uses || not CONCAT(a, b) for some versions, empty string is NULL — where('name', '') matches nothing. ->where('name', '') is a surprisingly common silent bug.

updateOrInsert uses MERGE. That's implemented and correct but MERGE requires unique constraints on the match keys; without one you'll get ORA-30926: unable to get a stable set of rows. Add the unique index.

Raw SQL with named binds. The wrapper supports :name placeholders, but mixing ? positional and :name named in the same statement throws. Stick to one style per query — Laravel's default of positional binds leaks if you ever concatenate DB::raw.

Debugging loop. The typical sequence:

DB::enableQueryLog();
... query ...
dump(DB::getQueryLog());                 // see bound SQL
dump(DB::connection('oracle')->getPdo()); // check driver name / DSN

Then map ORA codes: ORA-00942 = missing table or no grants, ORA-00933 = grammar produced non-Oracle syntax (often a LIMIT slipping through), ORA-01722 = invalid number (string column compared to int), ORA-02289 = sequence doesn't exist.

Config caching. php artisan config:cache will happily cache the DSN. Any Oracle-side env change (service_name, charset, NLS_LANG) requires a re-cache and a worker restart. NLS_LANG in particular is an OS-level env var the extension reads at connect time — it is not read from config/database.php. When you upgrade to v13, remember to php artisan config:clear before you restart workers — a cached config referencing a stale oci8 path is a classic "works locally, fails in prod" source after a PHP minor bump.

Extension points. If you need to tweak identifier quoting or add a grammar keyword (/*+ INDEX(...) */ hints go in DB::raw, hints on the query builder don't exist), extend:

use Jfelder\OracleDB\Query\OracleGrammar;

class HintedGrammar extends OracleGrammar
{
    // override compileSelect etc.
}

and rebind via a package provider or Connection::resolverFor('oracle', fn($cfg) => new OracleConnection(...)). Same for OracleConnector if you need a custom DSN (e.g. wallet/TNS alias connections — you can set database to a full (DESCRIPTION=...) string and leave host/port empty).

Docker/CI. The oci8 extension does not build on Alpine without compiling Instant Client — use php:8.x-fpm (Debian) with the Instant Client zip, or the prebuilt gvenzl/oracle-xe image plus shivammathur/setup-php@v2 with extensions: oci8 on Ubuntu runners.

Keep the driver version pinned and matched to your Laravel major. The project now publishes maintained branches per Laravel version: 13.x / ^13.0 requires PHP 8.3+ and Laravel 13, while 12.x (^12.1) stays on Laravel 12. This is the first release where a major bump was purely a framework/PHP support jump rather than a grammar change, but the branch split means you must pin deliberately — "jfelder/oracledb": "^12.1" on Laravel 12, "^13.0" on Laravel 13 — and check your framework upgrade against the real Oracle instance, not just migrate:fresh, because grammar regressions are still where the quiet bugs land. When planning a Laravel 13 upgrade, sequence it after the PHP 8.3 + oci8 build is green in CI; the package bump itself is trivial, the extension rebuild is not.