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

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.