Printing a large XMLTYPE value using 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 from StackOverflow 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;
Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • David  On October 25, 2016 at 16:10

    I am new to SQL and I am writing my first stored procedure in PL/SQL. To learn how my procedure is working I needed to be able to output an xml clob larger then 32k. So off to google where I found this code suggested by several sites. Everyone seems to have this basic loop and may have copied it from someone else. When I saw the exit condition at the beginning of the loop I thought the SQL loop must exit at the “end loop” because nobody would want to exit before outputting the last chunk. I tested and I found out the loop is exited at the exit condition and the last chunk is not output. Either I am not understanding something or it would seem that this code was never tested. When I moved the exit condition after the output line and before the offset increment it then exited after outputting the last chunk. Being new at this I searched the internet for a loop with the exit condition in that position, but could not find one. So I have a question. How could all the bloggers and such have the same code that appears to exit before outputting the last chunk? This is the first PL/SQL loop I have programmed so I am not comfortable contradicting the experts.

    • Ronald  On February 6, 2017 at 14:03

      Hi David,
      The way DBMS_LOB.SUBSTR works is that the 3rd argument is the starting point of the substr, for a length of v_chunk_size characters. So when offset > xmltype.length, it would fail. The loop prints chunks of the xml until the starting point for the substr moves beyond the end of the string.

      Kind regards,
      Ronald

  • Lucio Omar Rodriguez Lozano  On December 6, 2016 at 19:43

    Thanks Ronald! This was VERY useful to print a very large XML file that was driving me crazy! Kudos to you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: