Friday 16 March 2007

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.

1 comment:

Patrick Wolf said...

Hi Denes,

a really nice solution!

I looking through the code and saw two small improvements:
1) Use WWV_Flow.trim_sql to replace same default substitutions like #OWNER# in the SQL statement. It will also replace &XXX. references.

2) For the DBMS_XMLQUERY.getxml you can also use it as normal function, you don't have to select from dual.

BTW, maybe you should include a warning, that the code will not work for reports where the SQL statement is returned by a function.

Greetings
Patrick