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

We can set linesize to a big number. How big depends on the operating system, although I suspect most OSes will support up to 32K. This is from Windows....

SQL> set linesize 10000
SQL> set linesize 32767
SQL> set linesize 999999
SP2-0267: linesize option 999999 out of range (1 through 32767)
SQL>

If you have XML text longer than that, well you're out of luck.

You don't say why you're using SQL*Plus. Is this because you want the output on a client rather than the database server or because you don't know any other way to write out a file?

If you can write to the server there are alternatives. UTL_FILE still has a linesize limit of 32767 characters but at least we have do some programmatic wizardry to ensure that the line doesn't get chopped off in mid-tag.

However probably the best solution is to use DBMS_XSLPROCESSOR.CLOB2FILE() which is part of the XMLDB functionality.

---

For completeness the related SQL*Plus parameters recommended by copaX's colleague are:

PAGE - setting this to zero suppresses the headers and prevents page throws in the output
ECHO - controls whether the SQL statement is displayed when the script is run

Both PAGE' andECHO` are set to avoid extraneous text in the output. For the same reason the following parameter is often set

FEEDBACK - controls whether the rowcount is diplayed at the end of the query

LONG - controls the amount of LONG text displayed
LONGCHUNKSIZE - controls the amount of LONG text shown before the column wraps
TRIMSPOOL - removes whitespace which rights pads the line to LINESIZE value

LONG and LONGCHUNKSIZE shoudl be set to the same value to get the whole column displayed on a single output line.

All these paramaters and many more are explained in the documentation.

Answer 2

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;