Print XML to DBMS_OUTPUT.PUT_LINE or FND_FILE.PUT_LINE

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;	  

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