Oracle LOB 欄位操作完整指南：BFILE、BLOB、CLOB 實戰

LOB 類型概覽

Oracle 提供三種大型物件（LOB）型別：

• BFILE：唯讀，儲存於資料庫外部作業系統檔案，資料庫僅存檔案指標
• BLOB：二進位大型物件，儲存於資料庫內部，支援交易控制
• CLOB：字元大型物件，儲存單位元組或多位元組字元資料

BFILE 操作實例

插入 BFILE 資料

CREATE OR REPLACE PROCEDURE insert_book(filename VARCHAR2) AS
  book_file  BFILE := NULL;
  bookExists BOOLEAN := FALSE;
BEGIN
  book_file := BFILENAME('BOOK_TEXT', filename);
  bookExists := DBMS_LOB.FILEEXISTS(book_file) = 1;

  IF bookExists THEN
    INSERT INTO my_book_files 
    VALUES ((SELECT COUNT(*) FROM my_book_files) + 1, book_file);
    DBMS_OUTPUT.PUT_LINE('Insert success! file: ' || filename);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not exists! file: ' || filename);
  END IF;
EXCEPTION
  WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  WHEN DBMS_LOB.INVALID_DIRECTORY THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unknown Error: ' || SQLERRM);
END insert_book;
/

-- 呼叫範例
EXEC insert_book('chapter1.pdf');

BLOB 操作實例

從外部檔案載入 BLOB

CREATE OR REPLACE PROCEDURE insertImg(imgName VARCHAR2) IS
  v_file_loc BFILE;
  v_image    BLOB;
  nID        NUMBER;
  nImgSize   INTEGER;
  bFileExists BOOLEAN := FALSE;
BEGIN
  v_file_loc := BFILENAME('IMAGEDIR', imgName);
  bFileExists := DBMS_LOB.FILEEXISTS(v_file_loc) = 1;
  
  IF NOT bFileExists THEN
    DBMS_OUTPUT.PUT_LINE(imgName || ' not exists');
    RETURN;
  END IF;

  nImgSize := DBMS_LOB.GETLENGTH(v_file_loc);
  DBMS_OUTPUT.PUT_LINE(imgName || ' size is ' || nImgSize);

  -- 1. 先插入空 BLOB 並取得 LOB 定位器
  SELECT COUNT(*) + 1 INTO nID FROM imagetable;
  INSERT INTO imagetable(ID, image)
  VALUES (nID, EMPTY_BLOB())
  RETURNING image INTO v_image;

  -- 2. 開啟外部檔案並載入
  DBMS_LOB.FILEOPEN(v_file_loc, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LOADFROMFILE(v_image, v_file_loc, nImgSize);
  DBMS_LOB.FILECLOSE(v_file_loc);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_LOB.FILECLOSE(v_file_loc);
END insertImg;
/

-- 建立測試表
CREATE TABLE image_lob (
  t_id   VARCHAR2(5) NOT NULL,
  t_image BLOB NOT NULL
);

-- 呼叫測試
EXEC insertImg('tu1.jpg');

CLOB 操作實例

建立包含 CLOB 的表並插入資料

CREATE TABLE view_sites_info (
  site_id   NUMBER(3),
  audio     BLOB DEFAULT EMPTY_BLOB(),
  document  CLOB DEFAULT EMPTY_CLOB(),
  video_file BFILE DEFAULT NULL
);

-- 插入 CLOB 文字資料
DECLARE
  v_clob CLOB;
BEGIN
  INSERT INTO view_sites_info (site_id, document)
  VALUES (100, EMPTY_CLOB())
  RETURNING document INTO v_clob;
  
  DBMS_LOB.WRITE(v_clob, LENGTH('This is a writing example'), 1, 'This is a writing example');
  COMMIT;
END;
/

-- 查詢 CLOB
SELECT document FROM view_sites_info WHERE site_id = 100;

常見錯誤與解決

ORA-00932：資料類型不一致

錯誤原因：直接用 BFILENAME 插入 BLOB 欄位

-- 錯誤示範
INSERT INTO blobtest VALUES (1, BFILENAME('tempdir', 'test.jpg'));
-- ORA-00932: inconsistent datatypes

正確做法：BFILE 只能插入 BFILE 欄位；BLOB 需用 EMPTY_BLOB() + DBMS_LOB.LOADFROMFILE

關鍵 DBMS_LOB 程式包函數速查

最佳實務建議

• 大量載入 LOB 時使用 DBMS_LOB.LOADFROMFILE 效能最佳
• 操作內部 LOB 前務必先 RETURNING 取得 LOB 定位器
• BFILE 目錄物件需由 DBA 建立並授權 READ 權限
• 例外處理區塊中務必關閉已開啟的 BFILE，避免洩漏
• 考慮啟用 SECUREFILE 儲存選項（11g+）獲得更好壓縮/重複資料刪除效能