您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle BLOB 讀取實戰：DBMS_LOB + UTL_FILE 將二進位資料導出檔案

編輯：Oracle 資料庫基礎

BLOB 字段完整導出流程：從資料庫到檔案系統

Oracle BLOB (Binary Large Object) 存儲圖片、文件、多媒體等二進位資料。本文示範完整導出流程：確認物件 → 建立 Directory 權限 → 創建存儲過程分塊讀取 → 執行驗證，解決大檔案記憶體溢位與權限隔離問題。

前置環境確認

-- 1. 確認資料存在
COL fdesc FOR A30
SELECT fid, fname, fdesc FROM eygle_blob;
-- FID  FNAME       FDESC
-- 1    ShaoLin.jpg 少林寺-易經經
-- 2    DaoYing.jpg 倒映

Directory 物件與權限 (10g+ 標準做法)

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY BLOBDIR AS 'D:\oradata\Pic';
GRANT READ, WRITE ON DIRECTORY BLOBDIR TO eygle;

優勢：路徑集中管理、權限細粒度控制、避免硬編碼路徑、支援 ASM/雲儲存抽象。

分塊讀取導出存儲過程

CREATE OR REPLACE PROCEDURE eygle_dump_blob (
  p_iname  IN VARCHAR2,  -- 來源檔名 (表中 fname)
  p_oname  IN VARCHAR2   -- 目標檔名
) IS
  v_file       UTL_FILE.FILE_TYPE;
  v_buffer     RAW(32767);
  v_amount     CONSTANT PLS_INTEGER := 32767;
  v_pos        PLS_INTEGER := 1;
  v_blob       BLOB;
  v_blob_len   PLS_INTEGER;
BEGIN
  -- 1. 鎖定並讀取 BLOB 定位器
  SELECT fpic INTO v_blob FROM eygle_blob WHERE fname = p_iname;
  
  -- 2. 取得長度
  v_blob_len := DBMS_LOB.GETLENGTH(v_blob);
  
  -- 3. 開啟目標檔案 (wb = 二進位寫入/覆蓋)
  v_file := UTL_FILE.FOPEN('BLOBDIR', p_oname, 'wb', 32767);
  
  -- 4. 迴圈分塊讀寫
  WHILE v_pos < v_blob_len LOOP
    DBMS_LOB.READ(v_blob, v_amount, v_pos, v_buffer);
    UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);  -- TRUE = 自動 FLUSH
    v_pos := v_pos + v_amount;
  END LOOP;
  
  -- 5. 關閉檔案
  UTL_FILE.FCLOSE(v_file);
  
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;
END eygle_dump_blob;

關鍵 API 對照

API	用途	關鍵參數
DBMS_LOB.GETLENGTH	取得 LOB 位元組長度	返回 PLS_INTEGER (≤ 2GB) 或 DBMS_LOB.GETLENGTH 返回 INTEGER (≤ 128TB)
DBMS_LOB.READ	分塊讀取	(lob, amount, offset, buffer OUT RAW)
UTL_FILE.FOPEN	開啟檔案	(dir, file, mode, max_linesize) mode: 'r'/'w'/'a'/'rb'/'wb'/'ab'
UTL_FILE.PUT_RAW	寫入 RAW	(file, buffer, auto_flush)
UTL_FILE.FCLOSE	關閉檔案	釋放 OS 檔案控制代碼

執行驗證

SQL> HOST ls -l d:\oradata\Pic
total 7618
-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg

SQL> EXEC eygle_dump_blob('ShaoLin.jpg', '01.jpg');
PL/SQL procedure successfully completed.

SQL> HOST ls -l d:\oradata\Pic
total 11072
-rwxrwxrwa 1 Administrators SYSTEM 1768198 Apr 26 07:16 01.jpg   <-- 大小一致
-rwxrwxrwa 1 gqgai None 2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa 1 gqgai None 1768198 Apr 19 10:12 ShaoLin.jpg

大檔案 (>2GB) 處理增強版

v_blob_len  INTEGER;  -- 而非 PLS_INTEGER
v_amount    CONSTANT INTEGER := 32767;
v_pos       INTEGER := 1;

WHILE v_pos <= v_blob_len LOOP
  v_amount := LEAST(32767, v_blob_len - v_pos + 1);
  DBMS_LOB.READ(v_blob, v_amount, v_pos, v_buffer);
  UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
  v_pos := v_pos + v_amount;
END LOOP;

常見錯誤排查

錯誤	原因	解決
ORA-22288: file not found	Directory 路徑不存在/權限不足	OS 建立目錄、GRANT READ/WRITE ON DIRECTORY
ORA-06502: numeric or value error	PLS_INTEGER 溢位 (>2GB)	改用 INTEGER、DBMS_LOB.GETLENGTH 返回 INTEGER
ORA-29283: invalid file operation	檔案名非法、磁碟滿、超過檔案系統限制	檢查磁碟、檔案系統大檔支援 (NTFS/ext4/xfs)