| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Mailing Lists -> Oracle-L -> Suppressing a blank line in a union
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
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
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 s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
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
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
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> </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> </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> </DIV>
<DIV><FONT face="Courier New" size=2><SPAN class=452390723-17092002>select s.sid
sort_col1,<BR> 1 sort_col2,<BR> 0 sort_col3,<BR> 'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||<BR> 'Username/Schemaname= '||s.username||'/'||s.schemaname||chr(10)||chr(9)||<BR> 'Status = '||s.status||chr(10)||chr(9)||<BR> 'Client info'||chr(10)||chr(9)||chr(9)||<BR> 'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||<BR> 'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||<BR> 'Terminal Name =
'||p.spid||chr(10)||chr(9)||chr(9)||<BR> 'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||<BR> 'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||<BR> 'Program = '||p.program||chr(10)||chr(9)||chr(9)||<BR> 'Login Time = '||to_char(s.logon_time, 'YYYY/MM/DD:hh24:mi:ss')session_header<BR>from v$session s,<BR> v$process p<BR>where s.type != 'BACKGROUND'<BR> and s.paddr = p.addr<BR>union<BR>select e.sid
sort_col1,<BR> 2 sort_col2,<BR> 2 sort_col3,<BR> 'Wait Event Information'||chr(10)||chr(9)||<BR> 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> from v$session
sort_col1,<BR> 3 sort_col2,<BR> e.total_waits sort_col3,<BR> chr(9)||<BR>
rpad(to_char(e.event),30)||'('||<BR>
lpad(to_char(e.total_waits),05)||<BR>
lpad(to_char(e.total_timeouts),09)||<BR>
lpad(to_char(e.time_waited),07)||<BR>
lpad(to_char(e.average_wait),09)||<BR>
lpad(to_char(e.max_wait),09)||')' wait_info<BR>from v$session_event e<BR>where
e.sid in (select
( 12 0 2 0 1)<BR> direct path write (lob) (
5 0 0 0 0)<BR> async disk IO
( 4 0 0 0 0)<BR>
enqueue ( 3 0 9 3 8)<BR> log file switch completion (
1 0 4 4 4)</SPAN></FONT></DIV>
------_=_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
![]() |
![]() |