2 Replies Latest reply: Nov 23, 2012 6:34 AM by AlbertoFaenza RSS

    format 999.999

    user522961
      Hi,
      on 10g R2 how to display the numbers (and not to have #### instead) :
      SQL> set lines 120 trimspool on
      SQL> col event head "Waited for" format a30
      SQL> col total_waits head "Total|Waits" format 999.999
      SQL> col tw_ms head "Waited|for (ms)" format 999.999.99
      SP2-0246: cha¯ne de FORMAT non autorisÚe "999.999.99"
      SQL> col aw_ms head "Average|Wait (ms)" format 999.999.99
      SP2-0246: cha¯ne de FORMAT non autorisÚe "999.999.99"
      SQL> col mw_ms head "Max|Wait (ms)" format 999.999.99
      SP2-0246: cha¯ne de FORMAT non autorisÚe "999.999.99"
      SQL> select event, total_waits, time_waited*10 tw_ms,
        2         average_wait*10 aw_ms, max_wait*10 mw_ms
        3  from v$session_event
        4  where sid =152;
      
                                        Total      Waited     Average         Max
      Waited for                        Waits    for (ms)   Wait (ms)   Wait (ms)
      ------------------------------ -------- ----------- ----------- -----------
      class slave wait                  1.000         .00         .10         .00
      Streams AQ: waiting for time m ######## ########### ########### ###########
      anagement or cleanup tasks
      
      events in waitclass Other         1.000         .00         .00         .00
      Thank you
        • 1. Re: format 999.999
          ranit B
          >
          Total Waited Average Max
          Waited for Waits for (ms) Wait (ms) Wait (ms)
          ------------------------------ -------- ----------- ----------- -----------
          class slave wait 1.000 .00 .10 .00
          Streams AQ: waiting for time m ######## ########### ########### ###########
          anagement or cleanup tasks

          events in waitclass Other 1.000 .00 .00 .00
          Thank you

          Are you looking in TOAD or some other IDE??
          • 2. Re: format 999.999
            AlbertoFaenza
            Hi,

            format is not correct:

            You should set it in this way if you want 2 decimals and thousands separator
            format 999,999.99
            And when it is #### means that number is too large to fit in:
            set lines 120 trimspool on
            col event head "Waited for" format a30
            col total_waits head "Total|Waits" format 999,999
            col tw_ms head "Waited|for (ms)" format 999,999.99
            col aw_ms head "Average|Wait (ms)" format 999,999.99
            col mw_ms head "Max|Wait (ms)" format 999,999.99
            select event, total_waits, time_waited*10 tw_ms,
                   average_wait*10 aw_ms, max_wait*10 mw_ms
            from v$session_event
            where sid =152;
            
                                              Total      Waited     Average         Max
            Waited for                        Waits    for (ms)   Wait (ms)   Wait (ms)
            ------------------------------ -------- ----------- ----------- -----------
            class slave wait                      1        0.00         .10        0.00
            Streams AQ: waiting for time m       43  ##########  ##########  ##########
            anagement or cleanup tasks                                                 
            
            set lines 120 trimspool on
            col event head "Waited for" format a30
            col total_waits head "Total|Waits" format 999,999,999
            col tw_ms head "Waited|for (ms)" format 999,999,999.99
            col aw_ms head "Average|Wait (ms)" format 999,999,999.99
            col mw_ms head "Max|Wait (ms)" format 999,999,999.99
            select event, total_waits, time_waited*10 tw_ms,
                   average_wait*10 aw_ms, max_wait*10 mw_ms
            from v$session_event
            where sid =152;
            
            
                                                  Total          Waited         Average             Max
            Waited for                            Waits        for (ms)       Wait (ms)       Wait (ms)
            ------------------------------ ------------ --------------- --------------- ---------------
            class slave wait                          1            0.00             .10            0.00
            Streams AQ: waiting for time m           43   79,054,860.00    1,838,485.10    3,596,180.00
            anagement or cleanup tasks        
            Regards.
            Al

            Edited by: Alberto Faenza on Nov 23, 2012 1:33 PM