OraFAQ Forum: Client Tools » SPOOL question: How to eliminate blank lines in between queries in output

Home » Other » Client Tools » SPOOL question: How to eliminate blank lines in between queries in output (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SPOOL question: How to eliminate blank lines in between queries in output [message #453413]

Wed, 28 April 2010 12:00

BJerols
Messages: 14
Registered: March 2010

Junior Member

HI all, thanks in advance for your help. I tried searching the forum for spool without blank lines, etc to no avail so I figured I would ask the experts.

I am spooling to a text file some output for a client. The file has 4 queries in it, one creates a header row, another a comment row, another the data rows and finally a trailer.

Code looks something like this:

/*
Custom Extract
Project: Plan Data Extract
Product: EOWin 4.02 - Oracle db
Use: Script to create above extract and spool results to text file
Input Parameters: &1 Path and name of output file
*/

SET NEWPAGE NONE;
SET RECSEP OFF;
SET SPACE 0;
SET LINESIZE 100;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET HEADING OFF;
SET MARKUP HTML OFF;
SET TERMOUT ON;
SET TRIMSPOOL ON;
SET TRIM ON;

SPOOL &2;

--header
SELECT
'HDR,' || TO_CHAR(sysdate,'MMddyyyy') || 'Plan Data'
FROM
dual;

--CMT
SELECT
'CMT,Plan Num,Plan ID,Plan Name,Shares Allocated'
FROM
dual;

--Details
SELECT
'DAT' || ',' ||
p.plan_num || ',' ||
p.plan_id || ',' ||
p.plan_name || ',' ||
p.max_shares
FROM
planz p;

--Trailer
SELECT
'FTR' || ',' || count(p.plan_num)
FROM planz p;

SPOOL OFF;
SET TERMOUT ON;
SET VERIFY ON;
SET FEEDBACK ON;
SET HEADING OFF;
EXIT;

-------------------------

and the output looks like this:

HDR,04272010,Plan Data

CMT,Plan Num,Plan ID,Plan Name,Shares Allocated

DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999

FTR,4

but the client and I want the output to look like this with no blank lines in between the queries:

HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4

thanks again for any help!

Report message to a moderator