Unwanted new lines when spooling an sqlplus result to xml file

Question

I'm trying to extract some data from my database into a XML file. To do so, in use a bash script that calls sqlplus command and spool the result into a new file.

The problem happens once the result extracted. My xml file isn't valid anymore because there are some unwanted new lines added...

Here is an example of what I want:

<xml>
 <element>John</element>
 <element>some data</element>
 <element>a longer data line</element>
</xml>

And here is what I got:

<xml>
 <element>John</element>
 <element>some data</eleme
 nt>
 <element>a longer data 
 line</element>
</xml>

It seems that the longest lines are cut but I set linesize to 32767 in Sqlplus and these lines aren't so long...

This is what my sqlplus command looks like:

sqlplus -s {connection} << EOF
set serveroutput on size unlimited
set feedback off
set termout off
set linesize 32767

spool file.xml;

DECLARE
l_xmltype XMLTYPE;
l_ctx dbms_xmlgen.ctxhandle;
v_clob CLOB;
v_clob_length INTEGER;
pos INTEGER;
buffer VARCHAR2(32767);
amount BINARY_INTEGER := 32767;

BEGIN

l_ctx := dbms_xmlgen.newcontext('SELECT a.rowid, a.* FROM mytable a');
l_xmltype := dbms_xmlgen.getXmlType(l_ctx);
dbms_xmlgen.closeContext(l_ctx);

v_clob := l_xmltype.getClobVal;
v_clob_length := length(v_clob);

WHILE pos < clob_length LOOP
 dbms_lob.read(v_clob, amount, pos, buffer);
 dbms_output.put_line(buffer);
 pos := pos + amount;
END LOOP;

END;
/
EOF
Spool off;

Do you have any clue to help me solve this problem?

Thanks!

Answer 1

As @kfinity suggested, this is related to CLOB handling, but also to how dbms_output works. You're reading the CLOB in chunks of 32k, and writing each of those chunks out with put_line(), which appends a newline character after each 32k chunk. Those are not aligned with any existing line breaks within your XML document, so you get the original breaks, then extra ones - which appear somewhat random and mid-text, but are actually in predictable places.

An obvious solution is to switch from put_line() to put(), but that will break the maximum buffer size and throw something like "ORU-10028: line length overflow, limit of 32767 bytes per line".

Rather than reading in fixed 32k chunks, you could read one line at a time; the CLOB doesn't really understand lines as such, but you can look for line breaks, something like:

WHILE pos < v_clob_length LOOP
  -- read to next newline if there is one, rest of CLOB if not
  if dbms_lob.instr(v_clob, chr(10), pos) > 0 then
    amount := dbms_lob.instr(v_clob, chr(10), pos) - pos;
    dbms_lob.read(v_clob, amount, pos, buffer);
    pos := pos + amount + 1; -- skip newline character
  else
    amount := 32767;
    dbms_lob.read(v_clob, amount, pos, buffer);
    pos := pos + amount;
  end if;

  dbms_output.put_line(buffer);
END LOOP;

The if looks for a newline character, after the current position. If it finds one then the amount is calculated as the number of characters from the current position to that new line (or rather, minus one - as you don't want the newline itself), it reads that many characters, and then adjusts position by the amount read plus one (to skip the newline - which you don't want/need as put_line() adds one still).

If it doesn't find one then it reads up to 32k - hopefully only once; if there are more than that may chars left with no line break then it'll do a second read but still add that rogue extra new line and break that line. Not much you can do about that using dbms_output though, you would need to switch to utl_file writing to the server instead of spooling to the client.