René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

set numwidth [SQL*Plus]

set numwidth n
Sets the width for selected numbers in SQL*Plus to n (unless it is overridden by either set numformat or column format). 
set feedback off
set pagesize 0
set numformat ""

create table numwidth_ex (
  id    number primary key,
  num_1 number,
  num_2 number(5),
  num_3 number(7,2)
);

insert into numwidth_ex values (    0,     1    ,     1    ,     1    );
insert into numwidth_ex values (    1,    11    ,    11    ,    11    );
insert into numwidth_ex values (    2,   111    ,   111    ,   111    );
insert into numwidth_ex values (    3,  1111    ,  1111    ,  1111    );
insert into numwidth_ex values (    4, 11111    , 11111    , 11111    );
insert into numwidth_ex values (    5,     1.1  ,     1.1  ,     1.1  );
insert into numwidth_ex values (    6,     1.11 ,     1.11 ,     1.11 );
insert into numwidth_ex values (    7,     1.111,     1.111,     1.111);

insert into numwidth_ex select   8+id, - num_1  , - num_2  , - num_3 from numwidth_ex;
insert into numwidth_ex select  16+id, 5*num_1  , 5*num_2  , 5*num_3 from numwidth_ex;

select num_1, num_2, num_3 from numwidth_ex order by id;

         1          1          1
        11         11         11
       111        111        111
      1111       1111       1111
     11111      11111      11111
       1.1          1        1.1
      1.11          1       1.11
     1.111          1       1.11
        -1         -1         -1
       -11        -11        -11
      -111       -111       -111
     -1111      -1111      -1111
    -11111     -11111     -11111
      -1.1         -1       -1.1
     -1.11         -1      -1.11
    -1.111         -1      -1.11
         5          5          5
        55         55         55
       555        555        555
      5555       5555       5555
     55555      55555      55555
       5.5          5        5.5
      5.55          5       5.55
     5.555          5       5.55
        -5         -5         -5
       -55        -55        -55
      -555       -555       -555
     -5555      -5555      -5555
    -55555     -55555     -55555
      -5.5         -5       -5.5
     -5.55         -5      -5.55
    -5.555         -5      -5.55

set numwidth 5
select num_1, num_2, num_3 from numwidth_ex order by id;

    1     1     1
   11    11    11
  111   111   111
 1111  1111  1111
11111 11111 11111
  1.1     1   1.1
 1.11     1  1.11
1.111     1  1.11
   -1    -1    -1
  -11   -11   -11
 -111  -111  -111
-1111 -1111 -1111
##### ##### #####
 -1.1    -1  -1.1
-1.11    -1 -1.11
-1.11    -1 -1.11
    5     5     5
   55    55    55
  555   555   555
 5555  5555  5555
55555 55555 55555
  5.5     5   5.5
 5.55     5  5.55
5.555     5  5.55
   -5    -5    -5
  -55   -55   -55
 -555  -555  -555
-5555 -5555 -5555
##### ##### #####
 -5.5    -5  -5.5
-5.55    -5 -5.55
-5.56    -5 -5.55

set numwidth 20
select num_1, num_2, num_3 from numwidth_ex order by id;

                   1                    1                    1
                  11                   11                   11
                 111                  111                  111
                1111                 1111                 1111
               11111                11111                11111
                 1.1                    1                  1.1
                1.11                    1                 1.11
               1.111                    1                 1.11
                  -1                   -1                   -1
                 -11                  -11                  -11
                -111                 -111                 -111
               -1111                -1111                -1111
              -11111               -11111               -11111
                -1.1                   -1                 -1.1
               -1.11                   -1                -1.11
              -1.111                   -1                -1.11
                   5                    5                    5
                  55                   55                   55
                 555                  555                  555
                5555                 5555                 5555
               55555                55555                55555
                 5.5                    5                  5.5
                5.55                    5                 5.55
               5.555                    5                 5.55
                  -5                   -5                   -5
                 -55                  -55                  -55
                -555                 -555                 -555
               -5555                -5555                -5555
              -55555               -55555               -55555
                -5.5                   -5                 -5.5
               -5.55                   -5                -5.55
              -5.555                   -5                -5.55