Re: faster way to create XML export

Based on something like:

Select xmltype(cursor(select * from all_objects)).getClobVal() from dual;

You could do something like (UTLDATA is an Oracle directory alias)

SQL> DECLARE
2 rc sys_refcursor;
3 BEGIN
4 OPEN rc FOR SELECT * FROM ( SELECT rownum FROM dual CONNECT BY level < 500000 ); 5 dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , ‘UTLDATA’,'my.xml’); 6 end;
7 /

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>> Reply-To: "jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>" <jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>> Date: Tue, 2 Aug 2011 19:07:16 +0200
To: Oracle-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: faster way to create XML export

What's the fastest way to create an XML file on disk with a SQL query as input? We're on Oracle 10.2 and currently we're using the Java getXMLSAX() function with an output stream and an OracleXMLQuery object (from Oracle's Java API for XML). This is causing some performance problems... in fact it's accounting for over half the time in one particular long-running report which is important to the business. It really smells to me like something which could go a lot faster. Anyone have experience with this, and can suggest a better way?

-Jeremy

--

http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago

--

http://www.freelists.org/webpage/oracle-l Received on Tue Aug 02 2011 - 13:43:31 CDT