萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle CLOB 轉 BLOB 完整實現:DBMS_LOB.CONVERTTOBLOB 最佳實踐

在 Oracle 10g R2 及以上版本,DBMS_LOB.CONVERTTOBLOB 提供了高效、原生的 CLOB→BLOB 轉換能力,支援字符集轉換、大物件流式處理、臨時 LOB 管理。本文提供生產就緒的封裝函數,涵蓋異常處理、字符集參數化、大對象資源釋放最佳實踐。

標準轉換函數 (支援參數化字符集)

CREATE OR REPLACE FUNCTION clob_to_blob (
  p_clob       IN  CLOB,
  p_csid       IN  NUMBER DEFAULT DBMS_LOB.DEFAULT_CSID,  -- 目標字符集 ID,預設數據庫字符集
  p_lang_ctx   IN  INTEGER DEFAULT DBMS_LOB.DEFAULT_LANG_CTX,
  p_warning    OUT INTEGER
) RETURN BLOB DETERMINISTIC IS
  l_blob       BLOB;
  l_clob_len   NUMBER;
  l_dest_off   NUMBER := 1;
  l_src_off    NUMBER := 1;
  l_amount     INTEGER := DBMS_LOB.LOBMAXSIZE;
BEGIN
  -- 1. 空值/空 LOB 快速返回
  IF p_clob IS NULL OR DBMS_LOB.GETLENGTH(p_clob) = 0 THEN
    RETURN EMPTY_BLOB();
  END IF;

  l_clob_len := DBMS_LOB.GETLENGTH(p_clob);

  -- 2. 創建臨時 BLOB (緩存模式,會話級生命週期)
  DBMS_LOB.CREATETEMPORARY(l_blob, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.OPEN(l_blob, DBMS_LOB.LOB_READWRITE);

  -- 3. 執行轉換 (單次調用處理全部內容)
  DBMS_LOB.CONVERTTOBLOB(
    dest_lob     => l_blob,
    src_clob     => p_clob,
    amount       => l_amount,
    dest_offset  => l_dest_off,
    src_offset   => l_src_off,
    blob_csid    => p_csid,
    lang_context => p_lang_ctx,
    warning      => p_warning
  );

  -- 4. 關閉 LOB (釋放鎖定)
  DBMS_LOB.CLOSE(l_blob);

  RETURN l_blob;

EXCEPTION
  WHEN OTHERS THEN
    -- 確保異常路徑也釋放臨時 LOB
    IF l_blob IS NOT NULL AND DBMS_LOB.ISOPEN(l_blob) = 1 THEN
      DBMS_LOB.CLOSE(l_blob);
    END IF;
    IF l_blob IS NOT NULL THEN
      DBMS_LOB.FREETEMPORARY(l_blob);
    END IF;
    RAISE;
END clob_to_blob;
/
SHOW ERRORS

進階用法示例

1. 指定目標字符集 (如 AL32UTF8 → AL16UTF16)

DECLARE
  l_clob    CLOB := '中文測試內容';
  l_blob    BLOB;
  l_warn    INTEGER;
BEGIN
  l_blob := clob_to_blob(l_clob, p_csid => NLS_CHARSET_ID('AL16UTF16'), p_warning => l_warn);
  DBMS_OUTPUT.PUT_LINE('Warning: ' || l_warn);  -- 0=無警告, 1=字符丟失/替換
  DBMS_OUTPUT.PUT_LINE('BLOB Length: ' || DBMS_LOB.GETLENGTH(l_blob));
END;
/
-- 輸出: Warning: 0, BLOB Length: 14 (7 個字符 × 2 bytes/字符)

2. 流式分塊轉換 (超大 LOB > 1GB,避免 PGA 爆炸)

CREATE OR REPLACE PROCEDURE clob_to_blob_stream (
  p_src_clob   IN  CLOB,
  p_dest_blob  IN OUT NOCOPY BLOB,
  p_chunk_size IN  NUMBER DEFAULT 32767,  -- 32KB 塊
  p_csid       IN  NUMBER DEFAULT DBMS_LOB.DEFAULT_CSID
) IS
  l_src_off   NUMBER := 1;
  l_dest_off  NUMBER := 1;
  l_lang_ctx  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  l_warn      INTEGER;
  l_clob_len  NUMBER := DBMS_LOB.GETLENGTH(p_src_clob);
  l_amount    INTEGER;
BEGIN
  DBMS_LOB.CREATETEMPORARY(p_dest_blob, TRUE);
  DBMS_LOB.OPEN(p_dest_blob, DBMS_LOB.LOB_READWRITE);

  WHILE l_src_off <= l_clob_len LOOP
    l_amount := LEAST(p_chunk_size, l_clob_len - l_src_off + 1);
    DBMS_LOB.CONVERTTOBLOB(
      dest_lob     => p_dest_blob,
      src_clob     => p_src_clob,
      amount       => l_amount,
      dest_offset  => l_dest_off,
      src_offset   => l_src_off,
      blob_csid    => p_csid,
      lang_context => l_lang_ctx,
      warning      => l_warn
    );
    l_src_off  := l_src_off + l_amount;
    l_dest_off := l_dest_off + l_amount * (CASE WHEN p_csid = NLS_CHARSET_ID('AL16UTF16') THEN 2 ELSE 1 END);
  END LOOP;

  DBMS_LOB.CLOSE(p_dest_blob);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_LOB.ISOPEN(p_dest_blob) = 1 THEN DBMS_LOB.CLOSE(p_dest_blob); END IF;
    DBMS_LOB.FREETEMPORARY(p_dest_blob);
    RAISE;
END;
/
SHOW ERRORS

3. SQL 中直接調用 (SELECT 列表 / WHERE 條件)

-- 查詢文檔表,將 CLOB 內容轉為 BLOB 供前端下載
SELECT doc_id, doc_name, clob_to_blob(doc_content) AS doc_blob
FROM documents
WHERE doc_id = 12345;

-- 按二進制內容檢索 (如尋找特定二進制簽名)
SELECT doc_id FROM documents
WHERE DBMS_LOB.INSTR(clob_to_blob(doc_content), HEXTORAW('FFD8FFE0'), 1, 1) > 0;  -- JPEG 文件頭

關鍵參數解析

參數含義常用值
blob_csid目標 BLOB 字符集 IDNLS_CHARSET_ID('AL32UTF8') (UTF-8)、NLS_CHARSET_ID('AL16UTF16') (UTF-16BE)、DBMS_LOB.DEFAULT_CSID (數據庫字符集)
lang_context語言上下文,用於多字節字符集邊界處理DBMS_LOB.DEFAULT_LANG_CTX (自動),或調用 DBMS_LOB.GET_LANG_CONTEXT 獲取
warning輸出參數,0=成功,1=發生字符替換/丟失必須檢查,非 0 表示數據可能損壞
amount轉換字符數 (非位元組數)DBMS_LOB.LOBMAXSIZE 表示全部內容

避坑指南

  • ❌ 直接 UTL_RAW.CAST_TO_RAW(clob):僅適用單字節字符集,多字節會截斷/亂碼。
  • ❌ 忽略 WARNING 輸出:字符集不兼容時會靜默替換字符 (如 ),導致業務數據無聲損壞。
  • ❌ 不釋放臨時 LOB:會話級臨時 LOB 若不釋放,大量調用會導致臨時表空間耗盡 (ORA-01652)。
  • ✅ 使用 DETERMINISTIC 關鍵字:相同輸入返回相同輸出,優化器可緩存結果,適用於 SQL 重複調用場景。
  • ✅ 12c+ 可用 TO_BLOB(cs_id) 簡化語法:SELECT TO_BLOB(clob_col, NLS_CHARSET_ID('AL16UTF16')) FROM t; (但無 WARNING 輸出)。
copyright © 萬盛學電腦網 all rights reserved