| SQL Formating : Print Space between Column names [message #604695] |
Thu, 02 January 2014 23:12  |
sahadba
Messages: 59
Registered: September 2009
Location: Pune
|
Member |
|
|
Hi,
We have a task of sending a query output to client every three hours. I am using mailx for the same.
I have a problem with formatting the display;
Current Display :
BEGIN TIME ORA-01555 OUT-OF-SPACE MAX QUERY LENGTH
---------------- --------- ------------ ----------------
01/03/2014 05:55 0 0 102
01/03/2014 05:45 0 0 92
01/03/2014 05:35 0 0 298
01/03/2014 05:25 0 0 1057
01/03/2014 05:15 0 0 455
01/03/2014 05:05 0 0 148
01/03/2014 04:55 0 0 17
01/03/2014 04:45 0 0 521
01/03/2014 04:35 0 0 1135
01/03/2014 04:25 0 0 1347
01/03/2014 04:15 0 0 746
As you can see there is spacing problem, the column names are not equally spaced.
Below is the code snippet for the above display;
set pages 100
set underline off
column begin_time for a20 HEADING 'BEGIN TIME|----------------'
column ORA_01555 for 9 HEADING 'ORA-01555|---------'
column Out_Of_Space for 9 HEADING 'OUT-OF-SPACE|------------'
--column Max_Query_Length word_wrapped
column Max_Query_Length for 9999 HEADING 'MAX QUERY LENGTH|----------------'
select '########## ORA-01555 occurence details...... ##########' from dual;
set heading on
set feedback on
select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, lpad(SSOLDERRCNT,10) "ORA_01555", lpad(NOSPACEERRCNT,12) "Out_Of_Space", lpad(MAXQUERYLEN,16) "Max_Query_Length"
from gv\$undostat
where begin_time > to_date('01/02/2014 16:00:00','MM/DD/YYYY HH24:MI:SS');
Please help me out with the display.
Thank you
|
|
|
|
|
|
| Re: SQL Formating : Print Space between Column names [message #604740 is a reply to message #604695] |
Fri, 03 January 2014 10:47  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
I suspect this is what michel means:
SQL> SET LIN 70 PAGES 4999
SQL> TTITLE LE "########## ORA-01555 occurence details...... ##########"
SQL> COL "Begin Time" FOR A16
SQL> COL "ORA-01555" FOR A9
SQL> COL "Out-Of-Space" FOR A12
SQL> COL "Max Query Length" FOR A16
SQL> SELECT TO_CHAR ( Begin_Time, 'MM/DD/YYYY HH24:MI') "Begin Time"
2 , TO_CHAR ( Ssolderrcnt, '999,990') "ORA-01555"
3 , TO_CHAR ( Nospaceerrcnt, '999,990') "Out-Of-Space"
4 , TO_CHAR ( Maxquerylen, '999,999,990') "Max Query Length"
5 FROM Gv$undostat
6 WHERE Begin_Time > TO_DATE ( '01/03/2014 11:00:00', 'MM/DD/YYYY HH24:MI:SS')
7 /
########## ORA-01555 occurence details...... ##########
Begin Time ORA-01555 Out-Of-Space Max Query Length
---------------- --------- ------------ ----------------
01/03/2014 11:41 0 0 1,259
01/03/2014 11:31 0 0 1,259
01/03/2014 11:21 0 0 658
01/03/2014 11:11 0 0 41
01/03/2014 11:01 0 0 828
[Updated on: Fri, 03 January 2014 11:41] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
