Spooling output from SQL Plus - Blank lines 1

[ract]

I am trying to output the results of a query to a text file using the SPOOL in SQLPLUS. My code look like this:

SET head OFF
--set pagesize 40 
SET termout OFF
SET feedback OFF
SET verify OFF
SET timing OFF
SET echo OFF
SET linesize 80
SET trim ON

spool C:\outfile.txt;

select 'Column1: '||col1 ||'|P|2.3'|| Chr(10)||'Look for |'|| col2 || Chr(10) ||'More Text |'||Chr(13)
from (SELECT max(col1), col2
      FROM myTable
      group by col1)
;

--220,000 rows

The resulting text file should look like this:

Column1: theContentsOfCol1 |P|2.3
Look for | theContentsOfCol2
More Text |

Column1: theContentsOfCol1 |P|2.3
Look for | theContentsOfCol2
More Text |

...etc...


Unfortunately, I get a text file with 2 problems:

1. The results are placed on 4 lines
2. A blank line is placed every 13 lines

Note that the results in line 2 in the above example routinely span more than 80 characters. I have played with line size on page size but changing these settings cause more blank lines to appear. I finally broke down and used TOAD to save as to a text file, but I'd really like to know why the SPOOL did not work in SQLPLUS (especially why a blank line was placed every 13 lines)

Thanks

 

[SantaMufasa]

I'd really like to know why the SPOOL did not work in SQLPLUS (especially why a blank line was placed every 13 lines)

Notice, Ract that you have no setting for "SET PAGESIZE 0"...as a result, the default for PAGESIZE is 14, thus causing a page break every 13 lines. When you say "SET PAGESIZE 0", it eliminates pages breaks entirely, and it elimnates heading lines (which you don't want anyway for most text files).

Note that the results in line 2 in the above example routinely span more than 80 characters. I have played with line size on page size but changing these settings cause more blank lines to appear.

Let's say that you change your linesize to accommodate your longest line(s) about perhaps 200 characters. When you give the command, "SET LINESIZE 200", then SQL*Plus (lacking additional instructions) will output 200 characters per line (including padding blank spaces to 200 characters), regardless of actual number of output characters. This is what causes your unwanted "more blank lines to appear".


To suppress this unwanted behaviour, you can issue the following commands to SQL*Plus:

SET LINESIZE <some large value>
SET TRIMSPOOL ON

The "SET TRIMSPOOL ON" command causes trimming of unwanted trailing blanks spaces.

Let us know if this resolves your problems. If not, then post the residual issue(s), and we'll resolve that/those for you, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[ract]

Mufasa,

Your suggestions worked very well. I am a little embarrased that I burned half a day on this problem. Thanks for your help!

 

[SantaMufasa]

Next time, when you "burn" more than 10 minutes on the same issue, consider it a waste of your time, then post post your question here...We're always happy to help so long as you have tried to resolve it first.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”