Oracle 匯出 XML 實作指南：DBMS_XMLQuery 與 UTL_FILE 完整範例

適用版本

Oracle 9i、10g 驗證通過。

三步驟完成表資料匯出 XML 檔案

步驟 1：建立目錄物件

CREATE OR REPLACE DIRECTORY exp_dir AS '/opt';

注意：目錄需授予 Oracle OS 使用者寫入權限。

步驟 2：建立寫入 XML 的主程序

CREATE OR REPLACE PROCEDURE table2xml (
    result IN OUT NOCOPY CLOB,
    filename IN VARCHAR2
) IS
    xmlstr VARCHAR2(32767);
    line   VARCHAR2(2000);
    fhandle UTL_FILE.FILE_TYPE;
BEGIN
    fhandle := UTL_FILE.FOPEN('EXP_DIR', filename, 'w');
    xmlstr := DBMS_LOB.SUBSTR(result, 32767);
    LOOP
        EXIT WHEN xmlstr IS NULL;
        line := SUBSTR(xmlstr, 1, INSTR(xmlstr, CHR(10)) - 1);
        DBMS_OUTPUT.PUT_LINE('| ' || line);
        UTL_FILE.PUT_LINE(fhandle, line);
        xmlstr := SUBSTR(xmlstr, INSTR(xmlstr, CHR(10)) + 1);
    END LOOP;
    UTL_FILE.FCLOSE(fhandle);
END;
/

步驟 3：呼叫程序匯出指定查詢結果

DECLARE
    queryctx DBMS_XMLQUERY.CTXTYPE;
    result   CLOB;
BEGIN
    queryctx := DBMS_XMLQUERY.NEWCONTEXT('SELECT user_id, user_name FROM lq_xml_test');
    result   := DBMS_XMLQUERY.GETXML(queryctx);
    table2xml(result, 'table2xml.xml');
    DBMS_XMLQUERY.CLOSECONTEXT(queryctx);
END;
/

關鍵技術點說明

• DBMS_XMLQUERY：將 SQL 查詢結果轉為 XML 文件（CLOB 格式）
• UTL_FILE：在資料庫伺服器端讀寫作業系統檔案
• DBMS_LOB.SUBSTR：分塊處理大型 CLOB，避免記憶體溢位
• 逐行寫入：以換行符 CHR(10) 切分 XML 字串，逐行輸出檔案

擴展應用建議

1. 加入錯誤處理（EXCEPTION WHEN OTHERS）與日誌記錄
2. 支援動態傳入查詢 SQL、檔名、目錄參數化
3. 大量資料時考慮分批次查詢與流式寫入
4. 結合 DBMS_SCHEDULER 排程自動化匯出