Max column width in Oracle spool to file

Question

I have a script like this:

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET WRAP OFF
SET LINESIZE 32000
SET LONG 32000
SET LONGCHUNKSIZE 32000
SET SERVEROUT ON

SPOOL C:\Export.txt

SELECT XMLELEMENT("element1",xmlelement("element2",xmlattributes(.....)))
  FROM --TABLENAME--
 WHERE --CONDITIONS--

The output should be a file containing a list of rows with the complex xml inside, but when the length of the XML generated is longer than 2000, SQLPlus trims to 2000 and go to the next line.

There is a way to force SQLPlus to write all the data in the same line?

Answer 1

How about using getClobVal() to convert the output to a clob?

SELECT XMLELEMENT("element1",xmlelement("element2",xmlattributes(.....))).getClobVal()
  FROM --TABLENAME--
 WHERE --CONDITIONS--

Answer 2

Just add the following line right after the SET commands:

COL ColumnName FORMAT A32000

where ColumnName is a the alias for the XML column in your SELECT statement (you'll need to add an alias).

This sets the max width for that column, which is 2000 characters by default. Note that while you can set COL FORMAT as high as 60000 characters, the most you will actually ever get on one line with sqlplus is 32767, as this is the upper limit for LINESIZE.

Answer 3

Are you on Windows? I was having this same problem and neither of the other two answers helped me (directly, I had to do one more thing). Following the advice of this article on setting up SQL*Plus for Windows the author notes:

[note3] Set long big_number so you can see the definition of a complicated trigger or view, or text in any long or clob column.

I set mine as SET LONG 32000 (my longest line was a little over 2000 characters) and that solved the problem for me.