Oracle UTL_FILE 套件完整實戰：建立目錄授權、寫出檔案與讀入資料表的存儲過程範例

Oracle UTL_FILE 套件完整實戰

UTL_FILE 讀寫作業系統檔案，常用於資料匯出匯入、日誌記錄。本文示範目錄物件建立、授權、寫入檔案存儲過程、讀取檔案匯入表的完整範例與異常處理。

一、建立 Directory 物件並授權

-- Linux/Unix
CREATE OR REPLACE DIRECTORY BLOB_FILE_DIR AS '/home/oracle/export';
-- Windows
CREATE OR REPLACE DIRECTORY BLOB_FILE_DIR AS 'D:\test';

-- 授權給用戶
GRANT READ, WRITE ON DIRECTORY BLOB_FILE_DIR TO testUser;
GRANT EXECUTE ON UTL_FILE TO testUser;

注意：OS 目錄必須實際存在，路徑避免中文。

二、寫出檔案存儲過程

CREATE OR REPLACE PROCEDURE EXPORT_BLOB_TO_FILE (P_ID VARCHAR2) IS
  L_FILE      UTL_FILE.FILE_TYPE;
  L_BUFFER    VARCHAR2(4000);  -- VARCHAR2 上限 4000，超長需迴圈或用 RAW(32767)
  L_FILENAME  VARCHAR2(300);
BEGIN
  SELECT FILE_CONTENT, FILE_NAME
  INTO   L_BUFFER, L_FILENAME
  FROM   TEST_BLOB
  WHERE  FILE_ID = P_ID;

  L_FILE := UTL_FILE.FOPEN('BLOB_FILE_DIR', L_FILENAME, 'W');
  -- 模式：'R' 讀(須存在)、'W' 寫(覆蓋/新建)、'A' 追加(須存在)
  -- 二進位模式加 'B'：'RB','WB','AB' (BLOB 必須用二進位)

  UTL_FILE.PUT_LINE(L_FILE, L_BUFFER);  -- 寫入
  UTL_FILE.FCLOSE(L_FILE);              -- 關閉

EXCEPTION
  WHEN UTL_FILE.INVALID_PATH       THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid path');
  WHEN UTL_FILE.INVALID_MODE       THEN RAISE_APPLICATION_ERROR(-20002, 'Invalid mode');
  WHEN UTL_FILE.INVALID_OPERATION  THEN RAISE_APPLICATION_ERROR(-20003, 'Invalid operation');
  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN RAISE_APPLICATION_ERROR(-20004, 'Line too long');
  WHEN UTL_FILE.ACCESS_DENIED      THEN RAISE_APPLICATION_ERROR(-20005, 'Access denied');
  WHEN UTL_FILE.WRITE_ERROR        THEN RAISE_APPLICATION_ERROR(-20006, 'Write error');
  WHEN NO_DATA_FOUND               THEN RAISE_APPLICATION_ERROR(-20007, 'No data');
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(L_FILE) THEN UTL_FILE.FCLOSE(L_FILE); END IF;
    RAISE;
END EXPORT_BLOB_TO_FILE;

三、讀入檔案匯入表存儲過程

CREATE OR REPLACE PROCEDURE IMPORT_FILE_TO_BLOB (P_FILENAME VARCHAR2, P_ID VARCHAR2) IS
  L_FILE    UTL_FILE.FILE_TYPE;
  L_BUFFER  VARCHAR2(4000);
  L_TEXT    VARCHAR2(32767) := '';  -- 累積內容
BEGIN
  L_FILE := UTL_FILE.FOPEN('BLOB_FILE_DIR', P_FILENAME, 'R');

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(L_FILE, L_BUFFER);  -- 逐行讀取
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;  -- 讀完跳出
    END;
    L_TEXT := L_TEXT || L_BUFFER;
  END LOOP;

  INSERT INTO TEST_BLOB (FILE_ID, FILE_NAME, FILE_CONTENT)
  VALUES (P_ID, P_FILENAME, L_TEXT);
  COMMIT;

  UTL_FILE.FCLOSE(L_FILE);

EXCEPTION
  WHEN UTL_FILE.INVALID_PATH       THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid path');
  WHEN UTL_FILE.INVALID_MODE       THEN RAISE_APPLICATION_ERROR(-20002, 'Invalid mode');
  WHEN UTL_FILE.INVALID_OPERATION  THEN RAISE_APPLICATION_ERROR(-20003, 'Invalid operation');
  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN RAISE_APPLICATION_ERROR(-20004, 'Line too long');
  WHEN UTL_FILE.ACCESS_DENIED      THEN RAISE_APPLICATION_ERROR(-20005, 'Access denied');
  WHEN UTL_FILE.READ_ERROR         THEN RAISE_APPLICATION_ERROR(-20006, 'Read error');
  WHEN NO_DATA_FOUND               THEN RAISE_APPLICATION_ERROR(-20007, 'No data');
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(L_FILE) THEN UTL_FILE.FCLOSE(L_FILE); END IF;
    RAISE;
END IMPORT_FILE_TO_BLOB;

四、呼叫與驗證

BEGIN EXPORT_BLOB_TO_FILE('T1'); END;
BEGIN IMPORT_FILE_TO_BLOB('exported.txt', 'IMP001'); END;

-- 檢查匯入結果
SELECT FILE_ID, FILE_NAME, DBMS_LOB.GETLENGTH(FILE_CONTENT) FROM TEST_BLOB WHERE FILE_ID='IMP001';

五、關鍵提醒

• VARCHAR2 變數上限 4000，大檔案需用 UTL_FILE.PUT_RAW/GET_RAW 搭配 RAW(32767) 並分塊迴圈處理。
• 二進位模式 (後綴 B) 處理 BLOB 時必須使用。
• 完整異常處理確保檔案控制代碼正確關閉，避免資源洩漏。