Printing large XMLTYPE values with DBMS_OUTPUT

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;

Laat een reactie achter

Het e-mailadres wordt niet gepubliceerd.

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.

By using this site you acknowledge the use of cookies (which are mostly harmless, btw) More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below you are agreeing to these settings.

Close