Re: Suppressing a blank line in a union

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Wed, 18 Sep 2002 07:48:21 -0800
Message-ID: <F001.004D25B1.20020918074821@fatcity.com>

Dan,
It's the chr(10).

SQL> select ename||chr(10)||to_char(empno) title 2 from emp
3* where rownum <3

TITLE

SMITH
7369

ALLEN
7499

SQL> set recsep off
SQL> / TITLE

SMITH
7369
ALLEN
7499

hth,
Chaim

"Fink, Dan" <Dan.Fink_at_mdx.com>@fatcity.com on 09/17/2002 08:13:25 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

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
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
                 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
                 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_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

         enqueue                        (    3         0      9         3

8)
log file switch completion ( 1 0 4 4 4)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Chaim.Katz_at_Completions.Bombardier.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 Wed Sep 18 2002 - 10:48:21 CDT