Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Suppressing a blank line in a union

Suppressing a blank line in a union

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Tue, 17 Sep 2002 16:13:26 -0800
Message-ID: <F001.004D1E56.20020917161326@fatcity.com>


Content-Type: text/plain;
 charset="iso-8859-1"

I've got a nasty bit of sql using a union to provide a header line. SQL*Plus likes to place a blank line between the output of the unions and I want to get rid of it. I've done it before, but I have forgotten. I do recall that we never found documentation on it and 'stumbled' across the solution. The sql is below  

TIA,
Dan Fink  

column session_header format a1000

column sort_col1 noprint
column sort_col2 noprint
column sort_col3 noprint

set linesize 1001 trimspool on trimout on break on sort_col1 skip 3  

select s.sid sort_col1,

       1 sort_col2,
       0 sort_col3,
       'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||
       'Username/Schemaname=
'||s.username||'/'||s.schemaname||chr(10)||chr(9)||
       'Status = '||s.status||chr(10)||chr(9)||
       'Client info'||chr(10)||chr(9)||chr(9)||
       'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||
       'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||
       'Terminal Name = '||s.terminal||chr(10)||chr(9)||
       'dbServer info'||chr(10)||chr(9)||chr(9)||
       'O/S Process Id = '||p.spid||chr(10)||chr(9)||chr(9)||
       'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||
       'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||
       'Program = '||p.program||chr(10)||chr(9)||chr(9)||
       'Login Time = '||to_char(s.logon_time, 'YYYY/MM/DD:hh24:mi:ss')
session_header
from v$session s,

     v$process p
where s.type != 'BACKGROUND'
  and s.paddr = p.addr
union
select e.sid sort_col1,

       2 sort_col2,
       2 sort_col3,
       'Wait Event Information '||chr(10)||chr(9)||
       rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_header
from v$session_event e
where e.sid in (select s.sid
                from v$session s
                where s.type != 'BACKGROUND')
  and e.event not like 'SQL*N%'
union
select e.sid sort_col1,
       3 sort_col2,
       e.total_waits sort_col3,
       chr(9)||
       rpad(to_char(e.event),30)||'('||
       lpad(to_char(e.total_waits),05)||
       lpad(to_char(e.total_timeouts),09)||
       lpad(to_char(e.time_waited),07)||
       lpad(to_char(e.average_wait),09)||
       lpad(to_char(e.max_wait),09)||')' wait_info
from v$session_event e
where e.sid in (select s.sid
                from v$session s
                where s.type != 'BACKGROUND')
  and e.event not like 'SQL*N%'
order by sort_col1 asc, sort_col2 asc, sort_col3 desc;  

System ID = 57

        Username/Schemaname= SCOTT/TIGER
        Status = INACTIVE
        Client info
                O/S user = scott
                Machine Name = tiger
                Terminal Name = unknown
        dbServer info
                O/S Process Id = 26276
                O/S Username = oracle
                Terminal Name = UNKNOWN
                Program =  <mailto:oracle_at_tiger2> oracle_at_tiger2 (TNS V1-V3)
                Login Time = 2002/09/17:21:49:10
 
Wait Event Information
        Event                         (Waits/Timeouts/Waited/Avg Wait/Max
Wait)
                                                                    <---- I
want to get rid of this line.
        db file sequential read       ( 2799        0     18        0
0)
        log file sync                 (  409        0    244        1
19)
        db file scattered read        (  307        0      5        0
0)
        latch free                    (   12        0      2        0
1)
        direct path write (lob)       (    5        0      0        0
0)
        async disk IO                 (    4        0      0        0
0)
        enqueue                       (    3        0      9        3
8)
        log file switch completion    (    1        0      4        4
4)        

------_=_NextPart_001_01C25E9F.E22A4FF0
Content-Type: text/html;
 charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.50.4616.200" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial size=2><SPAN class=452390723-17092002>I've got a nasty bit
of sql using a union to provide a header line. SQL*Plus likes to place a blank line between the output of the unions and I want to get rid of it. I've done it before, but I have forgotten. I do recall that we never found documentation on it and 'stumbled' across the solution.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=452390723-17092002>The sql is
below</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN

class=452390723-17092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><SPAN

class=452390723-17092002>TIA,</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=452390723-17092002>Dan
Fink</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN

class=452390723-17092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face="Courier New" size=2><SPAN class=452390723-17092002>column
session_header format a1000<BR>column sort_col1 noprint<BR>column sort_col2 noprint<BR>column sort_col3 noprint<BR>set linesize 1001 trimspool on trimout on<BR>break on sort_col1 skip 3</SPAN></FONT></DIV>
<DIV><FONT face="Courier New"></FONT>&nbsp;</DIV>
<DIV><FONT face="Courier New" size=2><SPAN class=452390723-17092002>select s.sid

sort_col1,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 
sort_col2,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 
sort_col3,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'System ID = 
'||to_char(s.sid,'999')||chr(10)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Username/Schemaname= 
'||s.username||'/'||s.schemaname||chr(10)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Status = '||s.status||chr(10)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Client info'||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'O/S user = 
'||s.osuser||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Machine Name = 
'||s.machine||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Terminal Name = 

'||s.terminal||chr(10)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'dbServer
info'||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'O/S Process Id =
'||p.spid||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'O/S Username = 
'||p.username||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Terminal Name = 
'||p.terminal||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Program = 
'||p.program||chr(10)||chr(9)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
'Login Time = '||to_char(s.logon_time, 'YYYY/MM/DD:hh24:mi:ss') 
session_header<BR>from v$session s,<BR>&nbsp;&nbsp;&nbsp;&nbsp; v$process p<BR>where s.type != 'BACKGROUND'<BR>&nbsp; and s.paddr = p.addr<BR>union<BR>select e.sid
sort_col1,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 
sort_col2,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 
sort_col3,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Wait Event Information 
'||chr(10)||chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)' wait_header<BR>from v$session_event e<BR>where e.sid in (select s.sid<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from v$session
s<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where s.type != 'BACKGROUND')<BR>&nbsp; and e.event not like 'SQL*N%'<BR>union<BR>select e.sid
sort_col1,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3 
sort_col2,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; e.total_waits 
sort_col3,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
chr(9)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
rpad(to_char(e.event),30)||'('||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
lpad(to_char(e.total_waits),05)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
lpad(to_char(e.total_timeouts),09)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
lpad(to_char(e.time_waited),07)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
lpad(to_char(e.average_wait),09)||<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
lpad(to_char(e.max_wait),09)||')' wait_info<BR>from v$session_event e<BR>where 
e.sid in (select
s.sid<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from v$session
s<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where s.type != 'BACKGROUND')<BR>&nbsp; and e.event not like 'SQL*N%'<BR>order by sort_col1 asc, sort_col2 asc, sort_col3 desc;<BR></SPAN></FONT></DIV>
<DIV><FONT face="Courier New" size=2><SPAN
class=452390723-17092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT size=2><SPAN class=452390723-17092002><FONT face="Courier New">System
ID =&nbsp;&nbsp; 57<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Username/Schemaname= SCOTT/TIGER<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Status = INACTIVE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Client info<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; O/S user
=&nbsp;scott<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Machine Name
=&nbsp;tiger<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Terminal Name = unknown<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbServer info<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; O/S Process Id =
26276<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; O/S Username =
oracle<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Terminal Name =
UNKNOWN<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Program = </FONT><A href="mailto:oracle_at_tiger2"><FONT face="Courier New">oracle_at_tiger2</FONT></A><FONT face="Courier New"> (TNS V1-V3)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Login Time = 2002/09/17:21:49:10</FONT></SPAN></FONT></DIV>
<DIV><FONT face="Courier New"></FONT>&nbsp;</DIV>
<DIV><FONT face="Courier New" size=2><SPAN class=452390723-17092002>Wait Event
Information<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Event&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Waits/Timeouts/Waited/Avg Wait/Max Wait)</SPAN></FONT></DIV>
<DIV><SPAN class=452390723-17092002><FONT
face="Courier New">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</FONT><FONT face=Arial size=2>&lt;---- I want to get rid of this
line.</FONT></SPAN></DIV>
<DIV><FONT face="Courier New" size=2><SPAN
class=452390723-17092002>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db file sequential read&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( 2799&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; log file sync&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp; 409&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; 244&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
19)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; db file scattered read&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp; 307&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; latch free&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
(&nbsp;&nbsp; 12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; direct path write (lob)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;
5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; async disk IO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
(&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
enqueue&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
(&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
8)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; log file switch completion&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4)</SPAN></FONT></DIV>

<DIV><FONT face="Courier New"></FONT>&nbsp;</DIV>
<DIV><FONT face="Courier New" size=2><SPAN
class=452390723-17092002></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face="Courier New"></FONT>&nbsp;</DIV>
<DIV><FONT face="Courier New" size=2><SPAN class=452390723-17092002><FONT
face=Arial></FONT>&nbsp;</DIV></SPAN></FONT></BODY></HTML>

------_=_NextPart_001_01C25E9F.E22A4FF0--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Sep 17 2002 - 19:13:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US