Overload sprintf to work with clobs

[zhudock]

the existing code is a good foundation for handling the %s, but utl_lms.format_message won't accept clobs.

in my initial testing it looks like that function replaces '%%' with '%' first, and then does the sequential %s replacement.

[zhudock]

function sprintf(
    p_str in clob,
    p_s1 in varchar2 default null,
    p_s2 in varchar2 default null,
    p_s3 in varchar2 default null,
    p_s4 in varchar2 default null,
    p_s5 in varchar2 default null,
    p_s6 in varchar2 default null,
    p_s7 in varchar2 default null,
    p_s8 in varchar2 default null,
    p_s9 in varchar2 default null,
    p_s10 in varchar2 default null)
    return clob
  as
    l_return clob;
    -- To match behavior of utl_lms.format_message, only replace %s if char immediately before %s is not also %
    -- Need to prepend '\1' to regexp_replace output string so this char doesn't get lost
    c_substring_regexp constant varchar2(10) := '([^%])%[d|s]';

  begin
    l_return := p_str;

    -- Replace any explicitly named %s<n> with p_s<n>
    -- #23: Need to do in reverse so 10 processes before 1
    for i in reverse 1..10 loop
      l_return := regexp_replace(l_return, c_substring_regexp || i, '\1' ||
        case
          when i = 1 then p_s1
          when i = 2 then p_s2
          when i = 3 then p_s3
          when i = 4 then p_s4
          when i = 5 then p_s5
          when i = 6 then p_s6
          when i = 7 then p_s7
          when i = 8 then p_s8
          when i = 9 then p_s9
          when i = 10 then p_s10
          else null
        end,
        1,0,'c');
    end loop;

    -- Replace any remaining occurences of %s with p_s<n> (in order) and escape %% to %
    -- This time not in reverse
    for i in 1..10 loop
      l_return := regexp_replace(l_return, c_substring_regexp, '\1' ||
        case
          when i = 1 then p_s1
          when i = 2 then p_s2
          when i = 3 then p_s3
          when i = 4 then p_s4
          when i = 5 then p_s5
          when i = 6 then p_s6
          when i = 7 then p_s7
          when i = 8 then p_s8
          when i = 9 then p_s9
          when i = 10 then p_s10
          else null
        end,
        1,0,'c');
    end loop;
    l_return := regexp_replace(l_return,'%%','%');

    return l_return;

  end sprintf;

[zhudock]

Looks like the APEX_STRING.FORMAT API has implemented something very similar to sprintf now with 20 replacement string params. However, it's still missing CLOB support

[martindsouza]

@zhudock yes. I'm starting to notice some overlap with some of our APIs. One thing to remember is that APEX isn't on every DB server (though it should be ;-)