Oracle #

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

# Ekstrak ke direktori
sudo mkdir -p /opt/oracle
sudo unzip instantclient-basiclite-linux.x64-21.*.zip -d /opt/oracle/

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

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

# Saat ditanya path Instant Client:
# instantclient,/opt/oracle/instantclient_21_x

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

# Verifikasi
php -m | grep oci8

<?php
// 1. Easy Connect — cara paling sederhana
// Format: //host[:port]/service_name
$koneksi = oci_connect('username', 'password', '//localhost:1521/ORCL');

// 2. Easy Connect dengan opsi lengkap
$koneksi = oci_connect(
    username: 'hr',
    password:  'welcome1',
    connection_string: '//oracle-server.example.com:1521/XEPDB1',
    character_set: 'AL32UTF8', // UTF-8 untuk Unicode
);

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

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

// 5. Oracle Cloud (ATP/ADW) — butuh Wallet
putenv("TNS_ADMIN=/path/to/wallet");  // lokasi cwallet.sso dan tnsnames.ora
$koneksi = oci_connect('admin', 'MyCloudPassword123!', 'mydb_high');

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

// Tutup koneksi
oci_close($koneksi);

// oci_pconnect — persistent connection (reuse dari connection pool proses)
$koneksiPersisten = oci_pconnect('hr', 'welcome1', '//localhost/ORCL');

// oci_new_connect — selalu buat koneksi baru (bukan dari pool)
$koneksiBaru = oci_new_connect('hr', 'welcome1', '//localhost/ORCL');

<?php
// PDO dengan driver Oracle
$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 dengan bind variable
$sql  = "SELECT id, nama, email FROM employees WHERE department_id = :dept AND salary > :min_salary";
$stmt = oci_parse($koneksi, $sql);

// Bind variable ke 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 hasil
while ($row = oci_fetch_assoc($stmt)) {
    echo "{$row['ID']}: {$row['NAMA']} — {$row['EMAIL']}\n";
    // Perhatian: nama kolom Oracle secara default UPPERCASE!
}

oci_free_statement($stmt);

// Fetch semua sekaligus
oci_execute($stmt);
oci_fetch_all($stmt, $hasil, 0, -1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
print_r($hasil);

// INSERT dengan bind variable
$sql  = "INSERT INTO produk (id, nama, harga, created_at)
         VALUES (produk_seq.NEXTVAL, :nama, :harga, SYSDATE)";
$stmt = oci_parse($koneksi, $sql);

oci_bind_by_name($stmt, ':nama',  $nama,  100, SQLT_CHR);
oci_bind_by_name($stmt, ':harga', $harga, -1,  SQLT_FLT);

$nama  = 'Laptop Pro';
$harga = 15000000.0;

oci_execute($stmt);
echo oci_num_rows($stmt) . " baris diinsert\n";
oci_free_statement($stmt);

-- Buat sequence
CREATE SEQUENCE produk_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE    -- atau CACHE 20 untuk performa
    NOCYCLE;

-- Gunakan di INSERT
INSERT INTO produk (id, nama) VALUES (produk_seq.NEXTVAL, 'Laptop');

-- Dapatkan nilai yang baru dihasilkan
SELECT produk_seq.CURRVAL FROM DUAL;

-- Oracle 12c+: IDENTITY column (seperti AUTO_INCREMENT)
CREATE TABLE produk (
    id   NUMBER GENERATED ALWAYS AS IDENTITY,
    nama VARCHAR2(100)
);

<?php
// Insert dengan sequence dan ambil ID yang baru
$sql = "INSERT INTO produk (id, nama, harga)
        VALUES (produk_seq.NEXTVAL, :nama, :harga)
        RETURNING id INTO :new_id";

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

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

$nama  = 'Monitor 4K';
$harga = 5000000.0;

oci_execute($stmt);
echo "ID baru: $newId\n"; // nilai dari sequence

oci_free_statement($stmt);

<?php
// Definisi stored procedure di 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, nama, email, salary
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY nama;
END;
*/

// Panggil dari PHP
$sql  = "BEGIN get_employees_by_dept(:dept_id, :cursor); END;";
$stmt = oci_parse($koneksi, $sql);

$cursor = oci_new_cursor($koneksi); // buat cursor kosong

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

$deptId = 10;
oci_execute($stmt);
oci_execute($cursor); // eksekusi cursor yang sudah diisi stored procedure

// Iterasi hasil cursor
while ($row = oci_fetch_assoc($cursor)) {
    echo "{$row['NAMA']}: {$row['EMAIL']}\n";
}

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

<?php
// Eksekusi PL/SQL block
$plsql = "
BEGIN
    UPDATE produk
    SET harga = harga * (1 + :kenaikan_persen / 100)
    WHERE kategori = :kategori;

    INSERT INTO audit_log (aksi, detail, created_at)
    VALUES ('UPDATE_HARGA', 'Kategori: ' || :kategori || ', Kenaikan: ' || :kenaikan_persen || '%', SYSDATE);

    COMMIT;
END;
";

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

oci_bind_by_name($stmt, ':kenaikan_persen', $kenaikan, -1, SQLT_FLT);
oci_bind_by_name($stmt, ':kategori',        $kategori,  50, SQLT_CHR);

$kenaikan = 10.0;  // 10% kenaikan
$kategori = 'ELEKTRONIK';

oci_execute($stmt, OCI_NO_AUTO_COMMIT); // tanpa auto-commit
// OCI8 auto-commit secara default — OCI_NO_AUTO_COMMIT untuk transaksi manual

oci_commit($koneksi); // commit manual

oci_free_statement($stmt);

<?php
// Menyimpan teks panjang (CLOB)
$sql  = "INSERT INTO dokumen (id, judul, konten) VALUES (dok_seq.NEXTVAL, :judul, EMPTY_CLOB())
         RETURNING konten INTO :clob_var";
$stmt = oci_parse($koneksi, $sql);

$clob = oci_new_descriptor($koneksi, OCI_D_LOB); // buat LOB descriptor

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

$judul  = 'Laporan Tahunan 2024';
$konten = str_repeat("Isi laporan yang sangat panjang. ", 10000); // teks besar

oci_execute($stmt, OCI_NO_AUTO_COMMIT);

// Tulis konten ke LOB
$clob->saveFile = false;
$clob->write($konten);

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

// Membaca CLOB
$stmt = oci_parse($koneksi, "SELECT konten FROM dokumen 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);
$isiDokumen = $row['KONTEN']; // OCI_RETURN_LOBS otomatis load LOB ke string

oci_free_statement($stmt);

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

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

$namaFile = 'laporan.pdf';
oci_execute($stmt, OCI_NO_AUTO_COMMIT);

$blob->saveFile('/path/to/laporan.pdf'); // baca dari file dan simpan ke BLOB

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

<?php
// OCI8 auto-commit secara default — harus eksplisit untuk transaksi

function transferDana(mixed $koneksi, int $dariId, int $keId, float $jumlah): void
{
    // 1. Kurangi saldo pengirim (dengan SELECT FOR UPDATE — lock baris)
    $stmt = oci_parse($koneksi, "
        SELECT saldo FROM rekening WHERE id = :id FOR UPDATE
    ");
    oci_bind_by_name($stmt, ':id', $dariId, -1, SQLT_INT);
    oci_execute($stmt, OCI_NO_AUTO_COMMIT); // jangan commit dulu

    $row = oci_fetch_assoc($stmt);
    if (!$row || $row['SALDO'] < $jumlah) {
        oci_rollback($koneksi);
        throw new \DomainException("Saldo tidak mencukupi");
    }
    oci_free_statement($stmt);

    // 2. Kurangi
    $stmt = oci_parse($koneksi, "UPDATE rekening SET saldo = saldo - :jumlah WHERE id = :id");
    oci_bind_by_name($stmt, ':jumlah', $jumlah, -1, SQLT_FLT);
    oci_bind_by_name($stmt, ':id',     $dariId, -1, SQLT_INT);
    oci_execute($stmt, OCI_NO_AUTO_COMMIT);
    oci_free_statement($stmt);

    // 3. Tambah ke penerima
    $stmt = oci_parse($koneksi, "UPDATE rekening SET saldo = saldo + :jumlah WHERE id = :id");
    oci_bind_by_name($stmt, ':jumlah', $jumlah, -1, SQLT_FLT);
    oci_bind_by_name($stmt, ':id',     $keId,   -1, SQLT_INT);
    oci_execute($stmt, OCI_NO_AUTO_COMMIT);
    oci_free_statement($stmt);

    // 4. Commit semua perubahan
    oci_commit($koneksi);
}

// Rollback jika ada error
try {
    transferDana($koneksi, 1, 2, 500000.0);
} catch (\Throwable $e) {
    oci_rollback($koneksi);
    throw $e;
}

-- 1. Dual table — untuk query tanpa tabel
SELECT SYSDATE FROM DUAL;        -- Oracle
SELECT NOW();                    -- MySQL

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

-- 3. String: single quote only
SELECT 'teks' FROM DUAL;  -- Oracle (double quote untuk identifier)

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

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

-- 5. Nama kolom uppercase default
-- Oracle mengembalikan nama kolom sebagai UPPERCASE
-- Pastikan akses dengan uppercase: $row['NAMA'], bukan $row['nama']

-- 6. NULL concatenation
SELECT 'a' || NULL FROM DUAL; -- 'a' (NULL diabaikan di Oracle)
SELECT CONCAT('a', NULL);     -- NULL (di MySQL)

-- 7. Empty string = NULL di Oracle!
INSERT INTO t (col) VALUES (''); -- NULL di Oracle, bukan string kosong