April 14, 2007: printf in PL/SQL

Note: I have started to move some scripts and source code from this website to GitHub.

This affects some or all of the scripts found on this page. They should be found under
github.com/ReneNyffenegger/development_misc/ ... string_op.

I don't intend to maintain the scripts and or sources on this page any longer (so they might be outdated). But I will try to improve the code in the GitHub repository and accept Push Requests.

There are a few things in PL/SQL that I am really missing. One of these things is printf. I am not aware of any built-in functionality that remotely resembles the elegance of printf. Ok, lpad and rpad and to_char come to mind. But these functions are, IMHO, tedious to use, especially when it comes to handling null values.

Therefore, I have hacked up my own printf. I stress that it is a hack since there would probably be many features or improvements worth implementing, but that's for another day... So, if you come up with a better printf, please let me know, so I can put it on my website.

Anyway, there are a few differences between my printf and the standard c printf. For one, my printf handles only the format specifiers %d (numbers) and %s (strings). Also, the (optional) width (w) and precision (p) part in %w.pd behave differently. In c's printf, w denotes the total width of the resulting string. In my implementation, w denotes how many characters (digits) will be printed left of the dot. So, the resulting string of %7.3d will always be 10 digits wide, the sign inclusively. However, if I use %+7.3d, the resulting string is eleven characters wide. The behaviour of my printf is probably best demonstrated in this test case.

Since printf has a variable number of arguments, I create a nested table type to mimic this feature:

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

Now, sprintf, which returns the formatted string:

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;
/

The test case for 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;
/

Here's the printf function:

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

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


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