在 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
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/字符)
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
-- 查詢文檔表,將 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 字符集 ID | NLS_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 輸出:字符集不兼容時會靜默替換字符 (如 ),導致業務數據無聲損壞。DETERMINISTIC 關鍵字:相同輸入返回相同輸出,優化器可緩存結果,適用於 SQL 重複調用場景。TO_BLOB(cs_id) 簡化語法:SELECT TO_BLOB(clob_col, NLS_CHARSET_ID('AL16UTF16')) FROM t; (但無 WARNING 輸出)。