Oracle Jobs 匯出為執行腳本：DBMS_JOB.SUBMIT 重建指令產生器

需求

將 USER_JOBS 或 DBA_JOBS 中的排程工作匯出為可直接執行的 DBMS_JOB.SUBMIT PL/SQL 區塊，便於遷移或備份。

實作程式碼

DECLARE
    f           UTL_FILE.FILE_TYPE;
    jobn        INTEGER;
    s           VARCHAR2(4000);
    file_dir    VARCHAR2(100) := 'd:\temp';   -- 目錄需在 UTL_FILE_DIR 參數中
    file_name   VARCHAR2(100) := 'utl_file1.sql';
BEGIN
    f := UTL_FILE.FOPEN(file_dir, file_name, 'W');

    -- 寫入宣告區塊
    s := 'DECLARE' || CHR(13) || CHR(10) ||
         '  jobno NUMBER;' || CHR(13) || CHR(10) ||
         '  snd   VARCHAR2(20);' || CHR(13) || CHR(10) ||
         'BEGIN';
    UTL_FILE.PUT_LINE(f, s);

    -- 遍歷 JOBS（全庫請改 DBA_JOBS）
    FOR js IN (SELECT job, log_user, next_date, broken, interval, what FROM user_jobs) LOOP
        -- 先轉換 INTERVAL 運算式為日期字串
        s := 'EXECUTE IMMEDIATE ''SELECT TO_CHAR(' || js.interval ||
             ', ''''YYYY-MM-DD HH24:MI:SS'''') FROM DUAL'' INTO snd;';
        UTL_FILE.PUT_LINE(f, s);

        -- 產生 DBMS_JOB.SUBMIT 呼叫
        s := 'DBMS_JOB.SUBMIT(jobno, ' || CHR(39) || js.what || CHR(39) ||
             ', TO_DATE(snd, ' || CHR(39) || 'YYYY-MM-DD HH24:MI:SS' || CHR(39) || ')' ||
             ', ' || CHR(39) || js.interval || CHR(39) || ');';
        UTL_FILE.PUT_LINE(f, s);
    END LOOP;

    -- 結束區塊
    UTL_FILE.PUT_LINE(f, 'END;' || CHR(13) || CHR(10) || '/');
    UTL_FILE.FCLOSE(f);
END;
/

前置條件與限制

• 資料庫參數 UTL_FILE_DIR 必須包含目標目錄（或使用 Directory 物件，12c+ 建議）。
• 產生的檔案位於**資料庫伺服器端**，非客戶端。
• INTERVAL 運算式需可在 SQL 中直接執行 TO_CHAR，複雜 PL/SQL 運算式可能無法正確轉換。
• 僅匯出 WHAT、INTERVAL、NEXT_DATE，其他屬性（如 BROKEN）需手動補充。

改進方向

• 封裝為預存程序 EXPORT_JOBS(p_dir, p_file) 方便重複呼叫。
• 改用 DBMS_SCHEDULER（10g+ 推薦），功能更強大且支援匯出 DBMS_METADATA.GET_DDL。
• 輸出改寫為 INSERT INTO job_backup_table 持久化存儲，避免檔案權限問題。

輸出範例

DECLARE
  jobno NUMBER;
  snd   VARCHAR2(20);
BEGIN
EXECUTE IMMEDIATE 'SELECT TO_CHAR(SYSDATE+1, ''YYYY-MM-DD HH24:MI:SS'') FROM DUAL' INTO snd;
DBMS_JOB.SUBMIT(jobno, 'my_proc;', TO_DATE(snd, 'YYYY-MM-DD HH24:MI:SS'), 'SYSDATE+1');
END;
/