Export to XML format

I received couple of emails asking me how to export a report to an .xml file. Therefore I decided to create a small package and a demo application showing how to do that.

You can have a look here:

http://htmldb.oracle.com/pls/otn/f?p=31517:118

and read the corresponding thread here:

http://forums.oracle.com/forums/thread.jspa?messageID=1740225


Basically, you need to fetch the region source and replace bind variables with the v('ITEM') function first. Second, you use the DBMS_XMLQUERY.getxml package to transfer you SQL Query into a result set, formated as XML. After that, you just need to cut your result into smaller portions and create an .xml file out of it.

This will not work for lower versions than 2.2 or XE. For those, you need to replace the part of the package fetching the region source with this block:


SELECT plug_source
INTO v_sql
FROM wwv_flow_page_plugs
WHERE ID = LTRIM (p_region, 'R')
AND page_id = p_page_id
AND flow_id = p_app_id;


and have the appropriate grants granted by sys on

GRANT SELECT ON flows_020100.wwv_flow_page_plugs TO <your_schema>

and

CREATE OR REPLACE PUBLIC SYNONYM wwv_flow_page_plugs FOR
flows_020100.wwv_flow_page_plugs

where flows_020100 is your html_db schema.