Skip to Main Content
  • Questions
  • Spool is starting new line after 500 characters

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 16, 2016 - 11:39 pm UTC

Last updated: November 17, 2016 - 5:49 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I've got Spool working in that the output from a query is returning everything, but after 500 characters, it starts a new line.

So if if I had a line that was 502 characters ending with 'bananasarecool', then it would look something along the lines of :

bananasareco
ol


So Spool isn't truncating or removing any data, but simply starting a new line.
I am using the following settings. Is there anything I need to change to output without a limit to a single line?

SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET COLSEP '|'
SET TERMOUT OFF
SET VERIFY OFF
SET FLUSH OFF
SET BUFFER LENGTH 4000
SET ARRAYSIZE 200
SET LONG 10000



and Connor said...

I just did the following:

SET PAGESIZE 0
SET LINESIZE 32767
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET COLSEP '|'
SET TERMOUT OFF
SET VERIFY OFF
SET FLUSH OFF
SET BUFFER LENGTH 4000
SET ARRAYSIZE 200
SET LONG 10000
spool c:\temp\out.log
select rpad('x',5000,'x')||'y' from dual;
spool off

and the resultant file was 1 line, 5000 bytes long.

So

1) are you sure there isn't a carriage return in the data ?
2) no format masks are set for the column being selected
3) is it a clob ? If so, do set longchunksize 10000 as well

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here