Suppressing a blank line in a union

[Fink, Dan]

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 = oracle@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)