How to format a NUMBER column in Oracle?

Question

I try to spool output in a nice way, so i am formatting all VARCHAR2 columns to a5. However, my problematic columns are: SLOTNUM of type NUMBER(2) and FEEADJUSTMENT of type NUMBER(5,2). My code is:

column SLOTNUM format 999;
column FEEADJUSTMENT format 999.9;
.........
column [other VARCHAR2]     format a5;
select * from allTables;

Result is:

SLOTNUM DATEV ACTUA NOTES FEEADJUSTMENT TREAT NAME                                          
------- ----- ----- ----- ------------- ----- -----                                        
     12 19-JU 19-ju Treat           2.5 12345 Flu                                              
        N-13  n-13  ment                6     Shot                                       
              00:00 succe                                                                                   
              :00   ssful                                                                                    
                    l                                                                                       

     15 20-JU 20-ju Appar           5.5 12345 Flu                                                 
        N-13  n-13  atus                6     Shot                                                         
              02:00 broke                                                                                     
              :00                                                                                              

FEEADJUSTMENT is taking more space than it needs. Why doesn't format 999.9 truncate the FEEADJUSTMENT column?

Answer 1

Because it's using the length of the column header, as the documentation says:

A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater.

You can alias the column with a shorter name:

select slotnum as slotn, ...

You could also treat it as a string, but you'd still need an alias really:

select to_char(slotnum, 'FM9999') as slotn, ...

You could alias is as slotnum, if you preferred, and the use format a5, but I think that would be less clear.

I'm not sure I'd describe forcing everything to a5 as 'nice', particularly as you have values lomger than that which ware forcing column wrapping. A matter of taste, I suppose...

Answer 2

Format with this:

column SLOTNUM format a3;
column FEEADJUSTMENT format a5;