Oracle LOB 大物件操作全攻略：DBMS_LOB、動態 PL/SQL 處理 CLOB、BFILE 讀寫技巧

Oracle LOB (大物件) 類型與操作方法總覽

Oracle 提供四種 LOB 型別：BLOB (二進位)、CLOB (字元)、NCLOB (國家字元集)、BFILE (外部二進位檔案)。最大長度 4GB (受限於資料庫區塊大小)。本文整理 DBMS_LOB 套件操作、動態 PL/SQL 處理 CLOB 技巧、BFILE 唯讀特性等實戰內容。

LOB 型別快速比較

核心操作套件：DBMS_LOB

常用子程式：

• READ(lob_loc, amount, offset, buffer) - 讀取
• WRITE(lob_loc, amount, offset, buffer) - 寫入 (內部 LOB)
• APPEND(dest_lob, src_lob) - 附加
• ERASE(lob_loc, amount, offset) - 擦除
• COPY(dest_lob, src_lob, amount, dest_offset, src_offset) - 複製
• GETLENGTH(lob_loc) - 取得長度
• SUBSTR(lob_loc, amount, offset) - 截取子串
• FILEOPEN/FILECLOSE/FILEEXISTS - BFILE 專用

進階技巧：動態 PL/SQL 處理 CLOB 並傳遞表名/欄位名

場景：通用程序需針對不同表的 CLOB 欄位進行分段寫入 (超過 32767 字元)。

CREATE OR REPLACE PROCEDURE UPDATE_CLOB (
    P_TABLE_NAME  IN VARCHAR2,  -- 目標表名
    P_PK_NAME     IN VARCHAR2,  -- 主鍵欄位名
    P_CLOB_NAME   IN VARCHAR2,  -- CLOB 欄位名
    P_PK_VAL      IN NUMBER,    -- 主鍵值
    P_POS         IN NUMBER,    -- 起始位置 (以 32766 字元為區塊)
    P_CLOB_DATA   IN VARCHAR2   -- 要寫入的字串 (≤32767)
) IS
    V_LOB_LOC     CLOB;
    V_AMT         BINARY_INTEGER;
    V_POS         BINARY_INTEGER;
    V_QUERY_STR   VARCHAR2(1000);
BEGIN
    -- 計算實際寫入位置 (每區塊 32766 字元)
    V_POS := P_POS * 32766 + 1;
    V_AMT := LENGTH(P_CLOB_DATA);
    
    -- 動態查詢鎖定該列 FOR UPDATE
    V_QUERY_STR := 'SELECT ' || P_CLOB_NAME || ' FROM ' || P_TABLE_NAME ||
                   ' WHERE ' || P_PK_NAME || ' = :pk FOR UPDATE';
    
    EXECUTE IMMEDIATE V_QUERY_STR INTO V_LOB_LOC USING P_PK_VAL;
    
    -- 寫入 CLOB (需先初始化為 EMPTY_CLOB())
    DBMS_LOB.WRITE(V_LOB_LOC, V_AMT, V_POS, P_CLOB_DATA);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END UPDATE_CLOB;
/

使用流程

1. 先 INSERT 或 UPDATE 將 CLOB 欄位設為 EMPTY_CLOB()
2. 呼叫 UPDATE_CLOB 分段寫入 (每段 ≤ 32767 字元)
3. 超過 32767 字元時，迴圈呼叫並遞增 P_POS

SQL 直接操作 LOB 的限制

• SQL 僅能整欄位讀寫 (UPDATE SET CLOB_COL = '...')，無法操作片段
• PL/SQL DBMS_LOB 可隨機存取片段，效能佳
• JDBC/Oracle Call Interface (OCI) 可流式讀寫大 LOB，避免記憶體暴增

BFILE 操作範例

DECLARE
    V_BFILE BFILE := BFILENAME('MY_DIR_ALIAS', 'large_video.mp4');
    V_BLOB  BLOB;
BEGIN
    -- 建立目錄物件 (需 DBA)
    -- CREATE OR REPLACE DIRECTORY MY_DIR_ALIAS AS '/data/media';
    
    DBMS_LOB.FILEOPEN(V_BFILE, DBMS_LOB.FILE_READONLY);
    -- 讀取邏輯...
    DBMS_LOB.FILECLOSE(V_BFILE);
END;
/

效能與最佳實踐建議

1. 啟用 SecureFiles (11g+)：STORE AS SECUREFILE 提供壓縮、去重、加密、緩存優化。
2. 避免 SELECT * FROM LOB_TABLE：會讀取整個 LOB 導致網路/記憶體壓力，僅選取需要欄位。
3. CACHE / NOCACHE 設定：頻繁讀取的 LOB 設 CACHE，歸檔型設 NOCACHE。
4. 分段寫入大 CLOB：如上動態程序，每段 32K，避免 VARCHAR2 32767 限制。
5. 刪除 LOB 釋放空間：UPDATE SET LOB_COL = EMPTY_CLOB() 或 DBMS_LOB.FREETEMPORARY。