Re: faster way to create XML export
- From: Marco Gralike <Marco.Gralike@xxxxxxx>
- To: "jeremy.schneider@xxxxxxxxxxxxxx" <jeremy.schneider@xxxxxxxxxxxxxx>, Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Aug 2011 20:43:31 +0200
http://www.liberidu.com/blog/?p=365
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@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>>
Reply-To:
"jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>"
<jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>>
Date: Tue, 2 Aug 2011 19:07:16 +0200
To: Oracle-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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
Other related posts:
