Thanks for the question, Shay.
Asked: December 18, 2018 - 6:48 am UTC
Last updated: January 11, 2019 - 6:28 am UTC
Version: 10.1.2
Viewed 1000+ times
You Asked
Dear all,
I need to output a large xml file. I created a procedure and it outputs the file correctly when the number of records is small.
Code:
DECLARE
l_file UTL_FILE.file_type;
l_xmltype xmltype;
BEGIN
SELECT XMLRoot(
XMLElement("report",
....
....
....
)
), VERSION '1.0" encoding="UTF-8')
INTO l_xmltype
FROM
.... ; /*to change whenever required.. akj */
l_file := UTL_FILE.fopen ('XMLDIR', '123XML.XML', 'w');
UTL_FILE.PUT_LINE(l_file , l_xmltype.getStringVal( ));
UTL_FILE.fclose (l_file);
end;
Grateful if you could advise how to output a file with more records.
Thank you
It asks to use lob dt to store the large data.. if clob or blob is used instead of xmltype, it stills output error ' expected clob, got - '
and Connor said...
OK, I see what you mean now.
UTL_FILE is capped at 32k per line. So if your XML is larger than that you need to either:
1) not use UTL_FILE and perhaps just look at using DBMS_LOB.CLOB2FILE
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_LOB.html#GUID-006129FC-1335-4B9F-9D9A-40130F6D6F45 2) Or use UTL_FILE to read the clob in chunks and output them piecewise
eg
SQL> create or replace
2 procedure clob_to_file( p_dir in varchar2,
3 p_file in varchar2,
4 p_clob in clob )
5 as
6 l_output utl_file.file_type;
7 l_amt number default 32000;
8 l_offset number default 1;
9 l_length number default nvl(dbms_lob.getlength(p_clob),0);
10 BEGIN
11 l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
12 while ( l_offset < l_length )
13 loop
14 utl_file.put(l_output,
15 dbms_lob.substr(p_clob,l_amt,l_offset) );
16 utl_file.fflush(l_output);
17 l_offset := l_offset + l_amt;
18 end loop;
19 utl_file.new_line(l_output);
20 utl_file.fclose(l_output);
21 end;
22 /