April 14, 2007: printf in PL/SQL

create or replace type arg_list as table of varchar2(4000)
/

create or replace function sprintf(format in varchar2, parms in arg_list) return varchar2 is
  ret          varchar2(4000);
 
  cur_pos      number := 0;
  cur_format   varchar2(4000);
  len_format   number := length(format);
 
  left_aligned boolean;
  print_sign   boolean;
 
  cur_param    number := 0;
begin

  loop
    -- Iterating over each character in the format.
    -- cur_pos points to the character 'being examined'.
    cur_pos := cur_pos + 1;

    exit when cur_pos > len_format;
      -- The iteration is over when cur_pos is past the last character.

    if substr(format, cur_pos, 1) = '%' then
      -- A % sign is recognized.
     
      -- I assume the default: left aligned, sign (+) not printed
      left_aligned := false;
      print_sign   := false;
      
      -- Advance cur_pos so that it points to the character
      -- right of the %
      cur_pos := cur_pos + 1;

      -- 
      if substr(format, cur_pos, 1) = '%' then
         -- If % is immediately followed by another %, a literal
         -- % is wanted:
         ret := ret || '%';

         -- No need to further process the format (it is none)
         goto percent;
      end if;

      if substr(format, cur_pos, 1) = '-' then
         -- Current format will be left aligned
         left_aligned := true;
         cur_pos      := cur_pos + 1;
      end if;

      if substr(format, cur_pos, 1) = '+' then
         -- Print plus sign explicitely (only number, %d)
         print_sign := true;
         cur_pos    := cur_pos + 1;
      end if;

      -- Now, reading the rest until 'd' or 's' and
      -- store it in cur_format.
      cur_format := '';

      -- cur_param points to the corresponding entry
      -- in parms
      cur_param  := cur_param + 1;

      loop

        -- Make sure, iteration doesn't loop forever
        -- (for example if incorrect format is given)
        exit when cur_pos > len_format;

        if substr(format, cur_pos, 1) = 'd' then

          declare
            -- some 'local' variables, only used for %d
            chars_left_dot number;
            chars_rite_dot number;
            chars_total    number;
            dot_pos        number;
            to_char_format varchar2(50);
            buf            varchar2(50);
            num_left_dot   char(1) := '9';
          begin

          if cur_format is null then
             -- Format is: %d (maybe %-d, or %+d which SHOULD be
             -- handled, but isn't)
             ret := ret || to_char(parms(cur_param));
             -- current format specification finished, exit the loop
             exit;
          end if;

          -- does the current format contain a dot?
          -- dot_pos will be the position of the dot
          -- if it contains one, or will be 0 otherwise.
          dot_pos := instr(cur_format, '.');

          if substr(cur_format, 1, 1) = '0' then
            -- Is the current format something like %0...d?
            num_left_dot := '0';
          end if;

          -- determine how many digits (chars) are to be printed left
          -- and right of the dot.
          if dot_pos = 0 then
             -- If no dot, there won't be any characters rigth of the dot
             -- (and no dot will be printed, either)
             chars_rite_dot := 0;
             chars_left_dot := to_number(cur_format);
             chars_total    := chars_left_dot;
          else
             chars_rite_dot := to_number(substr(cur_format,    dot_pos + 1));
             chars_left_dot := to_number(substr(cur_format, 1, dot_pos - 1));
             chars_total    := chars_left_dot + chars_rite_dot + 1;
          end if;

          if parms(cur_param) is null then
             --  null h
             ret := ret || lpad(' ', chars_total);
             exit;
          end if;

          to_char_format := lpad('9', chars_left_dot-1, '9') || num_left_dot;
          
          if dot_pos != 0 then 
             -- There will be a dot
             to_char_format := to_char_format || '.' || lpad('9', chars_rite_dot, '9');
          end if;

          if print_sign then
             to_char_format := 'S' || to_char_format;
             -- The explicit printing of the sign widens the output one character
             chars_total := chars_total + 1;
          end if;

          buf := to_char(to_number(parms(cur_param)), to_char_format);

          if not print_sign then
            if substr(buf, 1, 1) = '-' or substr(buf, 1, 1) != ' ' then
              -- print a bunch of ! if the number doesn't fit the length
              buf := lpad('!', chars_total, '!');
            else
              buf := substr(buf, 2);
            end if;
          end if;

          if left_aligned then
            buf := rpad(trim(buf), chars_total);
          else
            buf := lpad(trim(buf), chars_total);
          end if;

          ret := ret || buf;

          exit;
          end;

        elsif substr(format, cur_pos, 1) = 's' then

          if cur_format is null then
            ret := ret || parms(cur_param);
            exit;
          end if;

          if left_aligned then
            ret := ret || rpad(nvl(parms(cur_param), ' '), to_number(cur_format));
          else
            ret := ret || lpad(nvl(parms(cur_param), ' '), to_number(cur_format));
          end if;

          exit;

        end if;

       cur_format := cur_format || substr(format, cur_pos, 1);

       cur_pos := cur_pos + 1;
      end loop;

    else
      ret := ret || substr(format, cur_pos, 1);
    end if;

    <<PERCENT>> null;
 
  end loop;

  return ret;

end sprintf;
/

set serveroutput on size 100000 format wrapped

declare
  procedure cmp(test_no in number, format in varchar2, args in arg_list, expected in varchar2) is
    gotten varchar2(4000);
  begin
    gotten := sprintf(format, args);

    if gotten != expected then

       dbms_output.put_line('test ' || test_no || ' failed');
       dbms_output.put_line('    gotten is: ' || gotten);
       dbms_output.put_line('  expected is: ' || expected);

    end if;

  end cmp;

begin
  dbms_output.put_line('sprintf test');

  -- Right aligning a string:
  cmp( 1,  'String: %20s', arg_list('hello world'), 'String:          hello world');

  -- Left aligning a string                                  12345678901234567890
  cmp( 2, 'String: %-20s', arg_list('hello world'), 'String: hello world         ');

  -- A left and a right aligned string                       ... 1234567890 1234567890 ...
  cmp( 3, '... %10s %-10s ...', arg_list('hello', 'world'), '...      hello world      ...');

  -- Strings and null                                                   ... 12345 12345 12345 12345 ...
  cmp( 4, '... %5s %5s %-5s %-5s ...', arg_list('a', null, 'b', null), '...     a       b           ...');

  -- Numbers
  cmp( 5, 'Numbers: %d,%d,%d'  , arg_list(  42,    0, -42), 'Numbers: 42,0,-42');
  cmp( 6, 'Numbers: %d,%d,%d'  , arg_list(  42, null, -42), 'Numbers: 42,,-42' );

  -- Right aligning numbers:                                             12345 12345 12345
  cmp( 7, 'Numbers: %5d,%5d,%5d'  , arg_list(  42,    0, -42), 'Numbers:    42,    0,  -42');
  cmp( 8, 'Numbers: %5d,%5d,%5d'  , arg_list(  42, null, -42), 'Numbers:    42,     ,  -42');

  -- Left aligning numbers:                                                 12345 12345 12345
  cmp( 9, 'Numbers: %-5d,%-5d,%-5d'  , arg_list(  42, null, -42), 'Numbers: 42   ,     ,-42  ');

  -- Fractions                                 12.12345
  cmp(10, '1/3: %2.5d',  arg_list( 1/3), '1/3:   .33333');
  cmp(11, '1/3: %02.5d', arg_list( 1/3), '1/3:  0.33333');
  cmp(12, '1/3: %2.5d',  arg_list(-1/3), '1/3:  -.33333');
  cmp(13, '1/3: %02.5d', arg_list(-1/3), '1/3: -0.33333');

  -- Fractions with signs                       S12.12345
  cmp(14, '1/3: %+2.5d',  arg_list( 1/3), '1/3:   +.33333');
  cmp(15, '1/3: %+02.5d', arg_list( 1/3), '1/3:  +0.33333');
  cmp(16, '1/3: %+2.5d',  arg_list(-1/3), '1/3:   -.33333');
  cmp(17, '1/3: %+02.5d', arg_list(-1/3), '1/3:  -0.33333');

  -- Recognizition of the %
  cmp(18, '%d %% of %d is: %d', arg_list(7, 68, 68/100*7), '7 % of 68 is: 4.76');

  -- Number doesn't fit the length
  cmp(19, '... %4d ...', arg_list(12345), '... !!!! ...');

end;
/

create or replace procedure printf (format in varchar2, parms in arg_list) is
begin
  dbms_output.put_line(sprintf(format, parms));
end;
/