How do I avoid random line-breaks in DBMS_Output, in Toad

Question

I have a largeish query, intended to produce a 6MB 'physical' XML document, (to be uploaded to another system) from a standard SQL database. The tail end of which looks like this:

    thisxmltype := dbms_xmldom.getXmlType(domdoc);
    dbms_output.enable(null);
    print_clob(thisxmltype.getClobVal );
    dbms_xmldom.freeDocument(domdoc);

I then save the output from dbms_output but after (every) 10,268 characters I'm finding a return character, right in the middle of an XML tag name, or sometimes value, which invalidates the whole thing.

Any suggestions on how to avoid this sort of result:

          <POSTCODE>EH99 1AA</POSTCODE>
          <RE
ASON>99</REASON>
          <REASON_OTHER/>

Can you give an example? I can't see how to use that in this case, since Oracle basically just returns the XML as a single string. — MikeB, Jun 10 '21 at 14:38
I haven't understood... do you want pretty print formatting? — Sayan Malakshinov, Jun 10 '21 at 15:10
@SayanMalakshinov I wasn't going for pretty as such, but that may well be a useful approach. I've added an anonymised example of what is going wrong - "REASON" must remain as one word to be valid XML — MikeB, Jun 11 '21 at 08:31

score 0 · Accepted Answer · answered Jun 10 '21 at 15:25

Extract() and XMLSERIALIZE allows you to pretty-print your xmltype easily:

declare
   xdata xmltype:=xmltype('<html><body><p>Hello world.</p></body></html>');
begin
   :res_1 := xdata.getclobval();
   :res_2 := xdata.extract('/').getclobval();
   select XMLSERIALIZE(Document xdata as CLOB INDENT SIZE = 2)
     into :res_3
   from dual;
end;
/

As you can see I get pretty-printed :res_2 and :res_3 here.

Full example:

SQL> var res_1 clob;
SQL> var res_2 clob;
SQL> var res_3 clob;
SQL>
SQL> declare
  2     xdata xmltype:=xmltype('<html><body><p>Hello world.</p></body></html>');
  3  begin
  4     :res_1 := xdata.getclobval();
  5     :res_2 := xdata.extract('/').getclobval();
  6     select XMLSERIALIZE(Document xdata as CLOB INDENT SIZE = 2)
  7       into :res_3
  8     from dual;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> col res_1 for a50;
SQL> col res_2 for a50;
SQL> col res_3 for a50;
SQL> print res_1;

RES_1
--------------------------------------------------
<html><body><p>Hello world.</p></body></html>

SQL> print res_2;

RES_2
--------------------------------------------------
<html>
  <body>
    <p>Hello world.</p>
  </body>
</html>



SQL> print res_3;

RES_3
--------------------------------------------------
<html>
  <body>
    <p>Hello world.</p>
  </body>
</html>

Again, this looks like a good start, but how do I use this with dbms_output.put_line() - or something similar? This gives me the first two lines or so, in the Script Output window, but I need to save the whole 6MB file somehow, and dbms_output seems to be my only option. — MikeB, Jun 11 '21 at 12:44
Forget my last, I had overlooked that print_clob() wasn't an Oracle function, but was defined somewhere in the middle of the code I had inherited. That needed to be re-written based on https://stackoverflow.com/questions/11647041/reading-clob-line-by-line-with-pl-sql so no working fine, with your res_2 example. — MikeB, Jun 11 '21 at 13:48
"script output"? Which tool? Have you configured "set long 10000000“? — Sayan Malakshinov, Jun 11 '21 at 16:09

How do I avoid random line-breaks in DBMS_Output, in Toad

1 Answers1