Oracle Certification, Database Administration, SQL, Application, Programming Reference Books
Formatting Columns in both Static and Dynamic Reports

Have you ever had to run a report in which the default names of the columns were just so weird that there was no way a user could decipher what that column heading meant?   If you have, and if you've looked at Oracles "SQL*Plus User Reference" (no technical info - just how to use SQL*Plus) then you've probably seen the column command.  Oracle does a good job at documenting it - they just don't play it up enough.  Well, that's what I'm here for!!!  So let's look at an example:
 
select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
from dual
/

This one produces the somewhat unintelligable output of ...

TO_CHAR(SYSDATE,'MM/DD/YYYYHH24:MI:SS')
---------------------------------------------------------------------------
06/17/1998 14:18:24

I imagine that this output looks exciting to the average user.  One way to get his/her attention is to dress up the SELECT using a simple column formatting command such as ....
 
col a_col heading "Date and Time" format a30
select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') a_col
from dual
/

Date and Time
------------------------------
06/17/1998 14:20:27

This looks good, but is rather run of the mill.  If you have to run the same SELECT twice, but you want the heading of the column to change with the second run, then try the following example:
 
col a_col heading "Date and Time - First Pass" format a30
select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') a_col
from dual
/
col a_col heading "Date and Time - Second Pass" format a30
/

Time and Date - First Pass
------------------------------
06/17/1998 14:22:12

Time and Date - Second Pass
------------------------------
06/17/1998 14:22:13

This solution looks so much more appealing than having to retype the same SELECT just to give it a different title.  Any other change to the title is possible here as well.  Just type it in and let it execute!

Return to : Oracle Database, SQL Tips