{"id":533,"date":"2017-06-23T22:15:19","date_gmt":"2017-06-23T21:15:19","guid":{"rendered":"http:\/\/www.psinke.nl\/blog\/?p=533"},"modified":"2017-06-23T22:15:19","modified_gmt":"2017-06-23T21:15:19","slug":"printing-large-xmltype-values-with-dbms_output","status":"publish","type":"post","link":"https:\/\/www.psinke.nl\/blog\/printing-large-xmltype-values-with-dbms_output\/","title":{"rendered":"Printing large XMLTYPE values with DBMS_OUTPUT"},"content":{"rendered":"<div class=\"afa2b3b851443650502c43e85c746c1f\" data-index=\"1\" style=\"float: none; margin:0px;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-4223201538046844\";\r\n\/* standaardblok *\/\r\ngoogle_ad_slot = \"9522527129\";\r\ngoogle_ad_width = 320;\r\ngoogle_ad_height = 50;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n<h2 class=\"entry-title\" style=\"text-align: left;\">Printing large XMLTYPE values with DBMS_OUTPUT<\/h2>\n<div class=\"entry-content\">\n<p>As a sort of primitive debugging-method I wanted to show the contents of an XMLTYPE variable using DBMS_OUTPUT.PUT_LINE.<\/p>\n<p>When you want to extract the contents of a large XMLTYPE you can use the method getClobVal(). There\u2019s another method, getStringVal(), but this will raise an exception if the contents of the XMLTYPE exceeds 32k (max size of varchar2).<\/p>\n<p>So, let\u2019s try:<\/p>\n<pre>dbms_output.put_line(xml_out.getclobval);<\/pre>\n<p>This gives:<\/p>\n<pre>Error at line 1\r\nORA-06502: PL\/SQL: numeric or value error\r\n<\/pre>\n<p>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<a href=\"http:\/\/stackoverflow.com\/questions\/26723362\/oracle-ora-06502-pl-sql-numeric-or-value-error-dbms-output\" target=\"_blank\" rel=\"noopener\">StackOverflow\u00a0<\/a>and came up with this code, which I\u2019m putting here also as a reminder to myself.<\/p>\n<pre>declare\r\n  xml_out xmltype;\r\n\r\n  -- Internal procedure to print a CLOB using dbms_output in chunks\r\n  procedure print_clob( p_clob in clob ) is\r\n    v_offset number := 1;\r\n    v_chunk_size number := 10000;\r\n  begin\r\n    loop\r\n      exit when v_offset &gt; dbms_lob.getlength(p_clob);\r\n      dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );\r\n      v_offset := v_offset + v_chunk_size;\r\n    end loop;\r\n  end print_clob;\r\nbegin\r\n  -- Equivalent to set serveroutput on size unlimited\r\n  dbms_output.enable(null);\r\n\r\n  MYPROC_THAT_PRODUCES_LARGE_XML(xml_out);\r\n\r\n  print_clob(xml_out.getClobVal);\r\nend;<\/pre>\n<\/div>\n<!--CusAds0-->\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>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\u2019s another method, getStringVal(), but this will raise an exception if the contents of&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[255,3],"tags":[],"class_list":["post-533","post","type-post","status-publish","format-standard","hentry","category-middleware","category-oracle-blog"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/comments?post=533"}],"version-history":[{"count":3,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/533\/revisions"}],"predecessor-version":[{"id":596,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/533\/revisions\/596"}],"wp:attachment":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/media?parent=533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/categories?post=533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/tags?post=533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}