I recently faced an issue in needing to generate a huge XML file without using the
UTL_FILE package.
Since I was working on Oracle EBS and/or Fusion, I can only use FND_FILE.PUT_LINE.
FND_FILE is a seeded package within Oracle Applications (both EBS and Fusion) and prints to a specific directory in the file system. Two common usages are:
fnd_file.put_line (fnd_file.OUTPUT, p_message );
and
fnd_file.put_line (fnd_file.LOG, p_message );
p_message has a data type of VARCHAR2. This means it's limited to 32767 bytes. So how can we print a huge XML when we're limited to 32767 characters? The Answer is Chunking.
Taken from a
StackOverflow post, we will chunk the XML into pieces that
FND_FILE.PUT_LINE can process.
Below is an Example:
declare
xml_out xmltype;
-- Internal procedure to print a CLOB using dbms_output in chunks
procedure print_clob( p_clob in clob ) is
v_offset number := 1;
v_chunk_size number := 10000;
begin
loop
exit when v_offset > dbms_lob.getlength(p_clob);
dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
v_offset := v_offset + v_chunk_size;
end loop;
end print_clob;
begin
-- generate an XML --
select xmlAgg(xmlconcat(xmlelement("dbaObjects"
, xmlelement("objName", object_name)
, xmlelement("objType", object_Type)
)))
into xml_out
from dba_objects;
print_clob(xml_out.getClobVal);
end;
However, if you want to use it in Oracle Apps (EBS and/or Fusion), you can use FND_FILE.PUT_LINE instead of DBMS_OUTPUT.
procedure print_clob( p_clob in clob ) is
v_offset number := 1;
v_chunk_size number := 10000;
begin
loop
exit when v_offset > dbms_lob.getlength(p_clob);
--dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
fnd_file.put_line (fnd_file.OUTPUT, dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
v_offset := v_offset + v_chunk_size;
end loop;
end print_clob;
