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;