PHP 操作 Oracle LOB (CLOB/BLOB) 完整教學

前言

Oracle VARCHAR2 限制 4000 位元組（約 2000 中文字），超過需改用 LOB：CLOB（字元）、BLOB（二進位）、BFILE（外部檔案）。PHP OCI8 擴充套件提供專用 API 操作 LOB，不可用一般 SQL 直接讀寫。

環境需求

• Oracle Client / Instant Client
• PHP oci8 擴充套件（extension=oci8）

資料表建立

CREATE TABLE pictures (
    id          NUMBER PRIMARY KEY,
    description VARCHAR2(100),
    mime        VARCHAR2(128),
    picture     BLOB
);
CREATE SEQUENCE pic_seq;

1. 插入 LOB (上傳圖片)

<?php
$conn = oci_connect($user, $pwd, $sid);

// 1. 準備 INSERT，使用 EMPTY_BLOB() 產生定位器，RETURNING 取回
$sql = "INSERT INTO pictures (id, description, mime, picture)
        VALUES (pic_seq.NEXTVAL, :desc, :mime, EMPTY_BLOB())
        RETURNING picture INTO :blob_loc";
$stid = oci_parse($conn, $sql);

// 2. 建立本地 LOB 描述符
$lob = oci_new_descriptor($conn, OCI_D_LOB);

// 3. 綁定參數
oci_bind_by_name($stid, ":desc",   $_POST['desc']);
oci_bind_by_name($stid, ":mime",   $_FILES['pic']['type']);
oci_bind_by_name($stid, ":blob_loc", $lob, -1, OCI_B_BLOB);

// 4. 執行
oci_execute($stid, OCI_DEFAULT); // 不自動 commit

// 5. 寫入檔案內容到 LOB
if ($lob->savefile($_FILES['pic']['tmp_name'])) {
    oci_commit($conn);
    echo "上傳成功";
} else {
    oci_rollback($conn);
    echo "上傳失敗";
}

// 6. 釋放
$lob->free();
oci_free_statement($stid);
oci_close($conn);
?>

關鍵點：必須用 EMPTY_BLOB() 建立空 LOB 定位器，再透過 RETURNING 取回，綁定 OCI_B_BLOB 後才能寫入。savefile() 直接讀取上傳暫存檔，避免記憶體溢位。

2. 讀取 LOB (顯示圖片)

<?php
$conn = oci_connect($user, $pwd, $sid);
$stid = oci_parse($conn, "SELECT mime, picture FROM pictures WHERE id = :id");
oci_bind_by_name($stid, ":id", $_GET['id']);
oci_execute($stid);

$row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_LOBS); // 關鍵：直接取值而非定位器
if ($row) {
    header("Content-Type: " . $row['MIME']);
    echo $row['PICTURE'];
}
oci_free_statement($stid);
oci_close($conn);
?>

呼叫方式：<img src="getpic.php?id=99">。OCI_RETURN_LOBS 讓 oci_fetch_array 直接回傳 LOB 內容，省去 load() 步驟。

3. 查詢 LOB (全文檢索)

Oracle 提供 DBMS_LOB 套件函式，如 INSTR、SUBSTR、LENGTH。

CREATE TABLE articles (
    id      NUMBER PRIMARY KEY,
    title   VARCHAR2(100),
    content CLOB
);

<?php
$conn = oci_connect($user, $pwd, $sid);
$keyword = "PHP中文用戶";
$sql = "SELECT id, title FROM articles
        WHERE DBMS_LOB.INSTR(content, :kw, 1, 1) > 0";
$stid = oci_parse($conn, $sql);
oci_bind_by_name($stid, ":kw", $keyword);
oci_execute($stid);
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
    echo $row['TITLE'] . "<br>";
}
oci_free_statement($stid);
oci_close($conn);
?>

DBMS_LOB.INSTR(lob_loc, pattern, offset, nth) 回傳第 n 次出現位置，>0 表示找到。

常見錯誤與注意事項

• LOB 欄位至少需 1 位元組，不可寫入空字串，否則 ORA-22275。
• 大量資料請用 OCI_DEFAULT 手動 oci_commit()，避免隱性 commit 導致效能下降。
• BFILE 僅支援 savefile()，且需建立 DIRECTORY 物件並授權。
• PHP 8.1+ 建議改用 oci8 3.x 版本，API 大同小異。