README

Text file encoding

• UTF-8

Code style formatter

• PSR-4

Installation

Run the command below to install via Composer:

composer require elie29/oci-driver

Getting Started

OCI Query Builder provides a lightweight builder to dynamically create SQL queries. It does not validate the query at all.

Select builder

// SELECT * FROM params ORDER BY name ASC
$sql = Select::start() // aka (new Select)
    ->column('*')
    ->from('params')
    ->orderBy('name')
    ->build();

Select builder with union

// SELECT p.id FROM params p UNION SELECT p.id FROM params_his p ORDER BY id ASC
$sql = Select::start() // aka (new Select)
    ->column('p.id')
    ->from('params', 'p')
    ->union()
    ->column('p.id')
    ->from('params_his', 'p')
    ->orderBy('id')
    ->build();

Delete builder

// DELETE FROM params WHERE id = 2
$sql = Delete::start() // aka (new Delete)
    ->from('params')
    ->where('id = 2')
    ->build();

Update builder

// UPDATE users u SET u.name = 'O''neil' WHERE u.user_id = 1
$sql = Update::start() // aka (new Update)
    ->table('users', 'u')
    ->set('u.name', Update::quote("O'neil"))
    ->where('u.user_id = 1')
    ->build();

Insert builder

// INSERT INTO params (user_id, name) VALUES (:id, :name)
$sql = Insert::start() // aka (new Insert)
    ->into('params')
    ->values([
        'user_id' => ':id',
        'name'    => ':name',
    ])
    ->build();

More examples are found in tests/OCI/Query/Builder folder.

Using OCI Driver Class

Using the factory

The Factory::create() method is the recommended way to create a Driver instance. It provides several benefits:

Purpose:

• Automatically configures the Oracle session with proper NLS settings
• Sets up the appropriate debugger based on the environment
• Ensures consistent date/time and numeric formats across all queries

Environment Parameter ($env):

The second parameter defines the execution environment and controls debugging behavior:

• 'prod' or 'production': Production mode - uses DebuggerDumb (no output)
• 'dev' or 'development' or 'test': Development mode - uses DebuggerDump (outputs debug information using VarDumper)

Session Configuration:

Factory automatically alters the Oracle session to set:

• NLS_DATE_FORMAT: 'YYYY-MM-DD' (ISO format)
• NLS_TIMESTAMP_FORMAT: 'YYYY-MM-DD HH24:MI:SS' (ISO format)
• NLS_NUMERIC_CHARACTERS: '.,' (dot as decimal separator, comma as thousand separator)

This eliminates the need to use TO_CHAR or TO_DATE in your SQL queries:

// Create a driver with development debugging
$connection = new Connection(USERNAME, PASSWORD, SCHEMA);
$driver = Factory::create($connexion->connect(), 'dev');

$sql = 'SELECT * FROM A1 WHERE N_DATE BETWEEN :YESTERDAY AND :TOMORROW';

$bind = (new Parameter())
    ->add(':YESTERDAY', date('Y-m-d', time() - 86400)) // Direct ISO format
    ->add(':TOMORROW', date('Y-m-d', time() + 86400));

$rows = $driver->fetchAllAssoc($sql, $bind);

Insert/Update Example

With Autocommit

Autocommit is the default behavior of OCI Driver:

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'INSERT INTO A1 (N_NUM) VALUES (5)';
$count = $driver->executeUpdate($sql);
echo $count; // displays 1

With Transaction

To start a transaction, you should use beginTransaction as follows:

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$driver->beginTransaction();

try {
   $count = $driver->executeUpdate($sql);
   $driver->commitTransaction();
   echo $count; // displays 1
} catch (DriverException $e) {
   echo $e->getMessage();
}

N.B.: When an error occurred using a transaction, rollback is called automatically.

Bind parameters

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'INSERT INTO A1 (N_CHAR, N_NUM, N_NUM_3) VALUES (:N1, :N2, :N3)';

$parameter = (new Parameter())
    ->add(':N1', 'c')
    ->add(':N2', 1)
    ->add(':N3', 0.24);

$count = $driver->executeUpdate($sql, $parameter);
echo $count; // displays 1

Fetch one row

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'SELECT * FROM A1 WHERE N_NUM = 2';

$row = $driver->fetchAssoc($sql);

N.B.: For binding parameters, follow the same insertion example above.

Fetch many rows

$connection = oci_pconnect('username', 'pass', 'schema', 'UTF8');
$driver = Factory::create($connection, 'dev');

$sql = 'SELECT * FROM A1';

$rows = $driver->fetchAllAssoc($sql);

N.B.: For binding parameters, follow the same insertion example above.

Running Oracle via Docker

If Oracle is not installed locally, use Docker to spin up an Oracle Free 23 container before running integration tests:

docker run -d \
  --name oracle-free \
  -p 1521:1521 \
  -e ORACLE_PASSWORD=MyPassword \
  -e APP_USER=myuser \
  -e APP_USER_PASSWORD=MyPassword \
  gvenzl/oracle-free:23-slim

• ORACLE_PASSWORD — required; sets the password for the SYS and SYSTEM accounts.
• APP_USER / APP_USER_PASSWORD — optional; creates a dedicated application user in XEPDB1. If omitted, connect as SYSTEM with ORACLE_PASSWORD instead.

Wait for the container to be ready (this can take 1–2 minutes):

docker logs -f oracle-free

The database is ready when the logs show:

DATABASE IS READY TO USE!

Then configure tests/integration/config-connection.php with:

• USERNAME → APP_USER if set, otherwise SYSTEM
• PASSWORD → APP_USER_PASSWORD if set, otherwise ORACLE_PASSWORD
• SCHEMA → localhost:1521/XEPDB1

To stop and remove the container when done:

docker stop oracle-free && docker rm oracle-free

Continue with the steps below to set up and run the integration tests.

Prepare for the test

Before launching integration tests, you should follow these steps:

1. Configure database connection

Rename config-connection.php.dist in ./tests/integration to config-connection.php:

cd tests/integration
cp config-connection.php.dist config-connection.php

Modify USERNAME, PASSWORD and SCHEMA according to your Oracle Database Information.

SCHEMA could be one of the following:

• IP:PORT/SID eg: 11.22.33.25:12005/HR
• SID name if you are executing the tests on the same database server or if you have a configured SID in tnsnames.ora

  • Use the following TNS:

    (DESCRIPTION =
        (ADDRESS =
        (PROTOCOL = TCP)(HOST = DATABASE_IP)(PORT=DATABASE_PORT)
        )
        (CONNECT_DATA =
        (SID=DATABASE_SCHEMA)(SERVER=DEDICATED|POOLED)
        )
    )
    

2. Create A1 and A2 tables

Option A: Using the automated setup script (Recommended)

Run the composer script to automatically create the required tables:

composer setup-tables

This will:

• Drop existing A1 and A2 tables if they exist
• Create fresh A1 and A2 tables with the correct structure
• Verify the connection and provide helpful error messages

Option B: Manual SQL creation

Alternatively, you can manually create the tables:

CREATE TABLE A1
(
    "N_CHAR" CHAR(5 BYTE
) ,
  "N_NUM"    NUMBER,
  "N_NUM_3"  NUMBER(6,3),
  "N_VAR"    VARCHAR2(4000),
  "N_CLOB"   CLOB,
  "N_DATE"   DATE,
  "N_TS"     TIMESTAMP,
  "N_LONG"   LONG
);

CREATE TABLE A2
(
    "N_LONG_RAW" LONG RAW
);

3. Run tests

Once the setup is complete, you can run the integration tests:

vendor/bin/phpunit --testsuite "Integration Tests"

Development Prerequisites

Test Structure

The project uses PHPUnit for testing with two test suites as configured in phpunit.xml.dist:

• Unit Tests (tests/units/): Fast, isolated tests that don't require database connections

  • Query Builder tests (Select, Insert, Update, Delete)
  • Helper utility tests (FloatUtils, ClauseInParamsHelper)

• Integration Tests (tests/integration/): Tests that require Oracle database connection

  • Driver tests (Connection, Query execution, Transaction management)
  • Factory and SessionInit tests

Composer commands

• setup-tables: Creates A1 and A2 test tables in the Oracle database (required for integration tests)
• test: Runs PHPUnit tests without coverage
• test-coverage: Runs PHPUnit unit tests with code coverage
• cover: Runs tests with coverage and starts a local server at http://localhost:5001

You can run specific test suites:

vendor/bin/phpunit --testsuite "Unit Tests"
vendor/bin/phpunit --testsuite "Integration Tests"