Printing large XMLTYPE values with DBMS_OUTPUT

As a sort of primitive debugging-method I wanted to show the contents of an XMLTYPE variable using DBMS_OUTPUT.PUT_LINE.

When you want to extract the contents of a large XMLTYPE you can use the method getClobVal(). There’s another method, getStringVal(), but this will raise an exception if the contents of the XMLTYPE exceeds 32k (max size of varchar2).

So, let’s try:

dbms_output.put_line(xml_out.getclobval);

This gives:

Error at line 1
ORA-06502: PL/SQL: numeric or value error

Hm. Apparently DBMS_OUTPUT is not equipped to work with CLOBs. So we have to resort to an old-school chunking-routine. I shamelessly copied this one fromStackOverflow and came up with this code, which I’m putting here also as a reminder to myself.

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
  -- Equivalent to set serveroutput on size unlimited
  dbms_output.enable(null);

  MYPROC_THAT_PRODUCES_LARGE_XML(xml_out);

  print_clob(xml_out.getClobVal);
end;