The example below shows the use of DBMS_XMLGen to output Oracle database table ( CLOB ) data in XML format. The PL/SQL code will output XML data on to the terminal/screen. The code can be enhanced to output the data to a named XML file using UTIL_FILE, which requires creation of database directory. When creating output file, data volume should be in managable limits of storage systems capacity and OS memory and CPU /processor should be able to handle file I/O . |
DECLARE
v_ctx DBMS_XMLGen.ctxHandle; v_xml CLOB; PROCEDURE display_xml(in_xmldata IN OUT NOCOPY CLOB) IS v_xmlstr VARCHAR2(32767); v_xline VARCHAR2(4000); BEGIN DBMS_OUTPUT.enable(99999999); v_xmlstr := DBMS_LOB.SUBSTR(in_xmldata,32767); WHILE v_xmlstr IS NOT NULL LOOP v_xline := substr(v_xmlstr,1,instr(v_xmlstr,CHR(10))-1); DBMS_OUTPUT.put_line(' '||v_xline); v_xmlstr := substr(v_xmlstr,instr(v_xmlstr,CHR(10))+1); END LOOP; END display_xml; BEGIN v_ctx := DBMS_XMLGen.newContext('SELECT q_tab.v_val month_num, DECODE(GREATEST(q_tab.v_val,4), q_tab.v_val, DECODE(GREATEST(q_tab.v_val,7), q_tab.v_val, DECODE(GREATEST(q_tab.v_val,10), q_tab.v_val,4,3),2),1) Quarter FROM (SELECT LEVEL v_val FROM DUAL CONNECT BY LEVEL <=12) q_tab'); v_xml := DBMS_XMLGen.GetXML(v_ctx); DBMS_XMLGen.setRowTag(v_ctx, ''); DBMS_XMLGen.closeContext(v_ctx); display_xml(v_xml); EXCEPTION WHEN OTHERS THEN RAISE; END; / |
69898