Home » SQL & PL/SQL » SQL & PL/SQL » How to extract out huge result set in xml format as .xml file (12.1.0.2)
How to extract out huge result set in xml format as .xml file [message #666124] Mon, 16 October 2017 14:56 Go to next message
philipebe
Messages: 5
Registered: September 2017
Junior Member
Hi,

I have this query in the below format (sample query). So the output of this query is in xml format. And when i run this query on TOAD, i see the output showing as (HUGECLOB), and the size of this CLOB is expected to be close to 2GB. And when i am trying to save this as xml file, it takes a lot time to load, but then it is showing empty. Looks like a read error.
But at least on TOAD i am able to see the output, but on SQLPLUS or SQLDeveloper, i am not even able to see this ouput.
When i change the filter in the where clause to split the output into multiple files, i am able to save the output in TOAD as xml file. But when trying to get the output as one file, which we need the output as one xml file, we are not able to save the output file.
So i thought i can write this output as xml file to the file system, instead of displaying as output to TOAD, so the file size doesn't matter.
I tried using utl_file options.....but for some reason....the file writes out but never completes.

Can someone help me re-write this sample below query using UTL_FILE or any other better way to write out this output to file system instead of outputting to TOAD.

WITH columna
AS (SELECT XMLAGG (
XMLELEMENT (
"columna",
xmlattributes (compressed_id AS "id"),
XMLFOREST (........)))
AS xml
FROM tablea,
tableb
AS (SELECT XMLELEMENT (
.
.
.
.
.
.
AS xml
FROM precinct p
CROSS JOIN tableb
CROSS JOIN tablec
CROSS JOIN tabled
CROSS JOIN tablee;
Re: How to extract out huge result set in xml format as .xml file [message #666125 is a reply to message #666124] Mon, 16 October 2017 15:01 Go to previous message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
http://lmgtfy.com/?q=oracle+write+xml+clob+to+file
Previous Topic: Composite Partitioning
Next Topic: Multiple Rows to a Single Row
Goto Forum:
  


Current Time: Tue Nov 21 02:17:04 CST 2017

Total time taken to generate the page: 0.01471 seconds