Spooling a query containing an XMLTYPE column using sqlplus

Question

I have a query that concatenates (manually by ||) a handful of columns, one of which is an XMLTYPE field (calling getClobVal() on it) . If I spool the query output using sqlplus with the following options set:

set long 30000;
set pagesize 0;
set feedback off;

I get all of the content as desired, but with cr/lf's after 80 characters, the first line's content stopping after 60 characters with 20 characters of whitespace, and the remaining lines' content stopping after 40 characters with 40 characters of whitespace.

If I add:

set linesize 120;

I get the same results with additional whitespace after the actual content (60 characters of whitespace on line 1, 80 characters of whitespace on remaining lines per row)

Is it possible to use sqlplus and spooling to create a csv output file for the results of a query containing large amounts of text?

I recognize that doing this by spooling is not the ideal method, but I need to be able to create this csv file in a way that can be executed on the command line or via a batch file.

Any suggestions?

Answer 1

A coworker who's had more experience with sqlplus came up with the following set block which gave me the outcome I was looking for:

set pagesize 0 echo off;
SET LINESIZE 30000 LONG 30000 LONGCHUNKSIZE 30000 Trimspool on;