Oracle UTL_FILE 導出存儲過程原始碼至作業系統檔案實作

前置設定

1. 修改 init.ora（或 spfile），設定 UTL_FILE_DIR 參數指向允許讀寫的目錄：
   UTL_FILE_DIR = /usr（需重啟實例生效）。
2. 目標目錄需授予 Oracle OS 用戶讀寫權限。

導出存儲過程原始碼範例

將用戶 TEMP 的程序 P1 原始碼逐行寫入 /usr/test.txt。

CREATE OR REPLACE PROCEDURE TEST IS
  file_handle  UTL_FILE.FILE_TYPE;
  stor_text    VARCHAR2(4000);
  n_max_line   NUMBER;
  i            NUMBER := 1;
BEGIN
  -- 取得該程序最大行號
  SELECT MAX(line) INTO n_max_line
  FROM all_source
  WHERE owner = 'TEMP' AND name = 'P1';

  -- 開啟檔案（附加模式 'a'）
  file_handle := UTL_FILE.FOPEN('/usr', 'test.txt', 'a');

  WHILE i <= n_max_line LOOP
    SELECT text INTO stor_text
    FROM all_source
    WHERE owner = 'TEMP' AND name = 'P1' AND line = i;

    UTL_FILE.PUT_LINE(file_handle, stor_text);
    i := i + 1;
  END LOOP;

  UTL_FILE.FCLOSE(file_handle);
  COMMIT;
END TEST;
/

EXEC TEST;

關鍵點說明

• ALL_SOURCE 視圖儲存所有可存取物件的原始碼（每行一筆）。
• UTL_FILE.FOPEN 模式：'r' 讀、'w' 寫（覆蓋）、'a' 附加。
• 大型程序建議分批處理，避免 VARCHAR2(4000) 截斷；12c+ 可用 VARCHAR2(32767)。
• 12c+ 推薦改用 DBMS_METADATA.GET_DDL 直接取得完整 DDL，更簡潔健壯。

結語

UTL_FILE 提供 PL/SQL 讀寫 OS 檔案能力，結合 ALL_SOURCE 可導出程式碼備份或異動追蹤。注意目錄權限與 UTL_FILE_DIR 設定，生產環境建議改用 Directory 物件（CREATE DIRECTORY）替代舊參數。