Skip to Main Content
  • Questions

  • Spool is starting new line after 500 characters

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here