Home » Other » Client Tools » SQL Formating : Print Space between Column names (11.2.0.4)
SQL Formating : Print Space between Column names [message #604695] Thu, 02 January 2014 23:12 Go to next message
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 #604698 is a reply to message #604695] Fri, 03 January 2014 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

column ORA_01555 for 9
lpad(SSOLDERRCNT,10) "ORA_01555"

Do you see the difference? Either format in "column" statement, either do it in the select expression, avoid doing it in both to prevent from discrepancies. (Check the other columns.)

One question: why do you use "set underline off" and then add the underline in your column header? Why don't you let SQL*Plus generate the correct underline itself?

Re: SQL Formating : Print Space between Column names [message #604740 is a reply to message #604695] Fri, 03 January 2014 10:47 Go to previous message
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

Previous Topic: Conversion of existing 4GL code in INFORMIX to PL/SQL
Next Topic: how to install repository for SQL Developer
Goto Forum:
  


Current Time: Thu Mar 28 13:15:05 CDT 2024