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;
For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)Subscribe to The Oracle Nerd on Youtube! https://www.youtube.com/c/OracleNerd
