RE: Suppressing a blank line in a union

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Wed, 18 Sep 2002 07:23:26 -0800
Message-ID: <F001.004D2503.20020918072326@fatcity.com>

Bingo! I turned recsep off and it did the trick. Thanks!

-----Original Message-----
Sent: Wednesday, September 18, 2002 2:37 AM To: ORACLE-L_at_fatcity.com
Cc: Dan.Fink_at_mdx.com

Dan,

I think set recsep off might do the trick as because that particular record wraps (because of the chr(10)) it inserts the blank line as default behaviour.
This would cause you to lose the desired blank lines between the other records though unless you added an extra chr(10) at the end. The other alternative would be to split it into two selects.

Iain Nicoll

-----Original Message-----
Sent: Wednesday, September 18, 2002 1:13 AM To: Multiple recipients of list ORACLE-L

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

-- 
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 Wed Sep 18 2002 - 10:23:26 CDT