Oracle #

# Linux — Download from: https://www.oracle.com/database/technologies/instant-client/downloads.html
# Choose: instantclient-basiclite-linux.x64-21.x.x.x.zip

# Extract to a directory
sudo mkdir -p /opt/oracle
sudo unzip instantclient-basiclite-linux.x64-21.*.zip -d /opt/oracle/

# Set the library path
echo /opt/oracle/instantclient_21_x | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
sudo ldconfig

# Install the OCI8 extension via PECL
sudo apt-get install php8.3-dev php-pear build-essential libaio1
sudo pecl install oci8

# When asked for the Instant Client path:
# instantclient,/opt/oracle/instantclient_21_x

# Enable the extension
echo "extension=oci8.so" | sudo tee /etc/php/8.3/mods-available/oci8.ini
sudo phpenmod oci8

# Verify
php -m | grep oci8

<?php
// 1. Easy Connect — the simplest way
// Format: //host[:port]/service_name
$connection = oci_connect('username', 'password', '//localhost:1521/ORCL');

// 2. Easy Connect with full options
$connection = oci_connect(
    username: 'hr',
    password:  'welcome1',
    connection_string: '//oracle-server.example.com:1521/XEPDB1',
    character_set: 'AL32UTF8', // UTF-8 for Unicode
);

// 3. TNS (Transparent Network Substrate) — via tnsnames.ora
// Define an entry in $ORACLE_HOME/network/admin/tnsnames.ora:
// MYDB =
//   (DESCRIPTION =
//     (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521))
//     (CONNECT_DATA = (SERVICE_NAME = mydb.example.com))
//   )
$connection = oci_connect('hr', 'welcome1', 'MYDB');

// 4. Full connection string (without tnsnames.ora)
$tns = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))";
$connection = oci_connect('hr', 'welcome1', $tns);

// 5. Oracle Cloud (ATP/ADW) — needs a Wallet
putenv("TNS_ADMIN=/path/to/wallet");  // location of cwallet.sso and tnsnames.ora
$connection = oci_connect('admin', 'MyCloudPassword123!', 'mydb_high');

if ($connection === false) {
    $error = oci_error();
    throw new \RuntimeException(
        "Oracle connection failed: [{$error['code']}] {$error['message']}"
    );
}

// Close the connection
oci_close($connection);

// oci_pconnect — persistent connection (reused from the process connection pool)
$persistentConnection = oci_pconnect('hr', 'welcome1', '//localhost/ORCL');

// oci_new_connect — always create a new connection (not from the pool)
$newConnection = oci_new_connect('hr', 'welcome1', '//localhost/ORCL');

<?php
// PDO with the Oracle driver
$dsn = 'oci:dbname=//localhost:1521/ORCL;charset=AL32UTF8';

$pdo = new PDO($dsn, 'hr', 'welcome1', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

<?php
// Query with bind variables
$sql  = "SELECT id, name, email FROM employees WHERE department_id = :dept AND salary > :min_salary";
$stmt = oci_parse($connection, $sql);

// Bind variables to the statement
oci_bind_by_name($stmt, ':dept',       $deptId,    -1, SQLT_INT);
oci_bind_by_name($stmt, ':min_salary', $minSalary, -1, SQLT_FLT);

$deptId    = 10;
$minSalary = 5000.0;

oci_execute($stmt);

// Fetch the results
while ($row = oci_fetch_assoc($stmt)) {
    echo "{$row['ID']}: {$row['NAME']} — {$row['EMAIL']}\n";
    // Note: Oracle column names are UPPERCASE by default!
}

oci_free_statement($stmt);

// Fetch everything at once
oci_execute($stmt);
oci_fetch_all($stmt, $results, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
print_r($results);

// INSERT with bind variables
$sql  = "INSERT INTO products (id, name, price, created_at)
         VALUES (product_seq.NEXTVAL, :name, :price, SYSDATE)";
$stmt = oci_parse($connection, $sql);

oci_bind_by_name($stmt, ':name',  $name,  100, SQLT_CHR);
oci_bind_by_name($stmt, ':price', $price, -1,  SQLT_FLT);

$name  = 'Pro Laptop';
$price = 15000000.0;

oci_execute($stmt);
echo oci_num_rows($stmt) . " rows inserted\n";
oci_free_statement($stmt);

-- Create a sequence
CREATE SEQUENCE product_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE    -- or CACHE 20 for performance
    NOCYCLE;

-- Use it in an INSERT
INSERT INTO products (id, name) VALUES (product_seq.NEXTVAL, 'Laptop');

-- Get the newly generated value
SELECT product_seq.CURRVAL FROM DUAL;

-- Oracle 12c+: IDENTITY column (like AUTO_INCREMENT)
CREATE TABLE products (
    id   NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(100)
);

<?php
// Insert with a sequence and get the new ID
$sql = "INSERT INTO products (id, name, price)
        VALUES (product_seq.NEXTVAL, :name, :price)
        RETURNING id INTO :new_id";

$stmt = oci_parse($connection, $sql);

oci_bind_by_name($stmt, ':name',    $name,  100);
oci_bind_by_name($stmt, ':price',   $price, -1, SQLT_FLT);
oci_bind_by_name($stmt, ':new_id',  $newId, -1, SQLT_INT); // output

$name  = '4K Monitor';
$price = 5000000.0;

oci_execute($stmt);
echo "New ID: $newId\n"; // value from the sequence

oci_free_statement($stmt);

<?php
// Stored procedure definition in Oracle (PL/SQL):
/*
CREATE OR REPLACE PROCEDURE get_employees_by_dept(
    p_dept_id   IN  NUMBER,
    p_cursor    OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT id, name, email, salary
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY name;
END;
*/

// Call from PHP
$sql  = "BEGIN get_employees_by_dept(:dept_id, :cursor); END;";
$stmt = oci_parse($connection, $sql);

$cursor = oci_new_cursor($connection); // create an empty cursor

oci_bind_by_name($stmt, ':dept_id', $deptId,  -1, SQLT_INT);
oci_bind_by_name($stmt, ':cursor',  $cursor,  -1, OCI_B_CURSOR); // bind the cursor

$deptId = 10;
oci_execute($stmt);
oci_execute($cursor); // execute the cursor filled by the stored procedure

// Iterate the cursor results
while ($row = oci_fetch_assoc($cursor)) {
    echo "{$row['NAME']}: {$row['EMAIL']}\n";
}

oci_free_statement($cursor);
oci_free_statement($stmt);

<?php
// Execute a PL/SQL block
$plsql = "
BEGIN
    UPDATE products
    SET price = price * (1 + :increase_percent / 100)
    WHERE category = :category;

    INSERT INTO audit_log (action, detail, created_at)
    VALUES ('UPDATE_PRICE', 'Category: ' || :category || ', Increase: ' || :increase_percent || '%', SYSDATE);

    COMMIT;
END;
";

$stmt = oci_parse($connection, $plsql);

oci_bind_by_name($stmt, ':increase_percent', $increase, -1, SQLT_FLT);
oci_bind_by_name($stmt, ':category',         $category,  50, SQLT_CHR);

$increase = 10.0;  // 10% increase
$category = 'ELECTRONICS';

oci_execute($stmt, OCI_NO_AUTO_COMMIT); // without auto-commit
// OCI8 auto-commits by default — OCI_NO_AUTO_COMMIT is for manual transactions

oci_commit($connection); // manual commit

oci_free_statement($stmt);

<?php
// Storing long text (CLOB)
$sql  = "INSERT INTO documents (id, title, content) VALUES (doc_seq.NEXTVAL, :title, EMPTY_CLOB())
         RETURNING content INTO :clob_var";
$stmt = oci_parse($connection, $sql);

$clob = oci_new_descriptor($connection, OCI_D_LOB); // create a LOB descriptor

oci_bind_by_name($stmt, ':title',    $title,  200, SQLT_CHR);
oci_bind_by_name($stmt, ':clob_var', $clob,   -1,  OCI_B_CLOB);

$title   = 'Annual Report 2024';
$content = str_repeat("Very long report content. ", 10000); // large text

oci_execute($stmt, OCI_NO_AUTO_COMMIT);

// Write the content to the LOB
$clob->saveFile = false;
$clob->write($content);

oci_commit($connection);
oci_free_statement($stmt);
$clob->free();

// Reading a CLOB
$stmt = oci_parse($connection, "SELECT content FROM documents WHERE id = :id");
oci_bind_by_name($stmt, ':id', $id, -1, SQLT_INT);
$id = 1;
oci_execute($stmt);

$row = oci_fetch_array($stmt, OCI_ASSOC + OCI_RETURN_LOBS);
$documentContent = $row['CONTENT']; // OCI_RETURN_LOBS automatically loads the LOB into a string

oci_free_statement($stmt);

// Storing a file (BLOB)
$sql  = "INSERT INTO files (id, name, data) VALUES (file_seq.NEXTVAL, :name, EMPTY_BLOB())
         RETURNING data INTO :blob_var";
$stmt = oci_parse($connection, $sql);
$blob = oci_new_descriptor($connection, OCI_D_LOB);

oci_bind_by_name($stmt, ':name',     $fileName, 200, SQLT_CHR);
oci_bind_by_name($stmt, ':blob_var', $blob,     -1,  OCI_B_BLOB);

$fileName = 'report.pdf';
oci_execute($stmt, OCI_NO_AUTO_COMMIT);

$blob->saveFile('/path/to/report.pdf'); // read from a file and save to the BLOB

oci_commit($connection);
$blob->free();
oci_free_statement($stmt);

<?php
// OCI8 auto-commits by default — must be explicit for transactions

function transferFunds(mixed $connection, int $fromId, int $toId, float $amount): void
{
    // 1. Reduce the sender's balance (with SELECT FOR UPDATE — row lock)
    $stmt = oci_parse($connection, "
        SELECT balance FROM accounts WHERE id = :id FOR UPDATE
    ");
    oci_bind_by_name($stmt, ':id', $fromId, -1, SQLT_INT);
    oci_execute($stmt, OCI_NO_AUTO_COMMIT); // don't commit yet

    $row = oci_fetch_assoc($stmt);
    if (!$row || $row['BALANCE'] < $amount) {
        oci_rollback($connection);
        throw new \DomainException("Insufficient balance");
    }
    oci_free_statement($stmt);

    // 2. Reduce
    $stmt = oci_parse($connection, "UPDATE accounts SET balance = balance - :amount WHERE id = :id");
    oci_bind_by_name($stmt, ':amount', $amount, -1, SQLT_FLT);
    oci_bind_by_name($stmt, ':id',     $fromId, -1, SQLT_INT);
    oci_execute($stmt, OCI_NO_AUTO_COMMIT);
    oci_free_statement($stmt);

    // 3. Add to the recipient
    $stmt = oci_parse($connection, "UPDATE accounts SET balance = balance + :amount WHERE id = :id");
    oci_bind_by_name($stmt, ':amount', $amount, -1, SQLT_FLT);
    oci_bind_by_name($stmt, ':id',     $toId,   -1, SQLT_INT);
    oci_execute($stmt, OCI_NO_AUTO_COMMIT);
    oci_free_statement($stmt);

    // 4. Commit all changes
    oci_commit($connection);
}

// Rollback on error
try {
    transferFunds($connection, 1, 2, 500000.0);
} catch (\Throwable $e) {
    oci_rollback($connection);
    throw $e;
}

-- 1. Dual table — for queries without a table
SELECT SYSDATE FROM DUAL;        -- Oracle
SELECT NOW();                    -- MySQL

-- 2. Concatenation
SELECT 'Hello' || ' ' || name FROM employees; -- Oracle
SELECT CONCAT('Hello', ' ', name) FROM users; -- MySQL

-- 3. Strings: single quotes only
SELECT 'text' FROM DUAL;  -- Oracle (double quotes are for identifiers)

-- 4. Pagination
-- Oracle 12c+: OFFSET ... FETCH
SELECT * FROM products ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle 11g: ROWNUM (trickier)
SELECT * FROM (
    SELECT p.*, ROWNUM rn FROM (
        SELECT * FROM products ORDER BY id
    ) p WHERE ROWNUM <= 30
) WHERE rn > 20;

-- 5. Uppercase column names by default
-- Oracle returns column names as UPPERCASE
-- Access with uppercase: $row['NAME'], not $row['name']

-- 6. NULL concatenation
SELECT 'a' || NULL FROM DUAL; -- 'a' (NULLs are ignored in Oracle)
SELECT CONCAT('a', NULL);     -- NULL (in MySQL)

-- 7. Empty string = NULL in Oracle!
INSERT INTO t (col) VALUES (''); -- NULL in Oracle, not an empty string