Oracle 資料匯出 XML 檔案：DBMS_XMLQUERY 與 UTL_FILE 實作

適用版本

Oracle 9i、10g 驗證通過。

三步驟完成匯出

1. 建立目錄物件（需 OS 目錄授予 Oracle 寫入權限）
2. 建立寫入 XML 的 PL/SQL 程序
3. 呼叫 DBMS_XMLQUERY 產生 XML 並寫入檔案

完整程式碼

-- 1. 目錄物件
CREATE OR REPLACE DIRECTORY exp_dir AS '/opt';

-- 2. 寫入程序
CREATE OR REPLACE PROCEDURE table2xml (
    p_xml   IN OUT NOCOPY CLOB,
    p_file  IN VARCHAR2
) IS
    v_line   VARCHAR2(2000);
    v_xml    VARCHAR2(32767);
    v_fh     UTL_FILE.FILE_TYPE;
BEGIN
    v_fh := UTL_FILE.FOPEN('EXP_DIR', p_file, 'W');
    v_xml := DBMS_LOB.SUBSTR(p_xml, 32767);
    LOOP
        EXIT WHEN v_xml IS NULL;
        v_line := SUBSTR(v_xml, 1, INSTR(v_xml, CHR(10)) - 1);
        UTL_FILE.PUT_LINE(v_fh, v_line);
        v_xml := SUBSTR(v_xml, INSTR(v_xml, CHR(10)) + 1);
    END LOOP;
    UTL_FILE.FCLOSE(v_fh);
END;
/

-- 3. 匯出執行區塊
DECLARE
    v_ctx    DBMS_XMLQUERY.CTXTYPE;
    v_xml    CLOB;
BEGIN
    v_ctx := DBMS_XMLQUERY.NEWCONTEXT('SELECT user_id, user_name FROM lq_xml_test');
    v_xml := DBMS_XMLQUERY.GETXML(v_ctx);
    table2xml(v_xml, 'table2xml.xml');
    DBMS_XMLQUERY.CLOSECONTEXT(v_ctx);
END;
/

關鍵點說明

• DBMS_XMLQUERY.NEWCONTEXT 接受任意 SELECT 陳述式
• GETXML 回傳 CLOB，含完整 XML 標籤結構
• UTL_FILE 需目錄物件權限，逐行寫入避免 32K 限制
• 10g+ 可改用 DBMS_XMLGEN 效能更佳