Home » SQL & PL/SQL » Client Tools » SPOOL question: How to eliminate blank lines in between queries in output (Oracle 10g)
SPOOL question: How to eliminate blank lines in between queries in output [message #453413] Wed, 28 April 2010 12:00 Go to next message
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!
Re: SPOOL question: How to eliminate blank lines in between queries in output [message #453414 is a reply to message #453413] Wed, 28 April 2010 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
col ord noprint
select 1 ord, 1st query
union all
select 2, 2nd query
union all
select 3, 3rd query
...
order by ord
/


Regards
Michel
Re: SPOOL question: How to eliminate blank lines in between queries in output [message #453415 is a reply to message #453413] Wed, 28 April 2010 12:26 Go to previous messageGo to next message
BJerols
Messages: 14
Registered: March 2010
Junior Member
That worked perfectly Michel!


Merci beaucoup!!!
Re: SPOOL question: How to eliminate blank lines in between queries in output [message #453416 is a reply to message #453415] Wed, 28 April 2010 12:44 Go to previous message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Problem in 3 rd party tool
Next Topic: Key board shortcuts
Goto Forum:
  


Current Time: Mon Oct 21 00:48:12 CDT 2019