A safe truncating string append

[janihur]

I suggest a string append subprogram that silently will truncate too long strings. This is usually wanted in reporting and logging scope.

Usage example:

declare
  v_str varchar2(5);
begin
  for i in 1 .. 10
  loop
    append(v_str, 'A');
  end loop;
  -- the value of v_str will be 'AA...' (assuming ... is a truncated mark)
end;
/

If the idea is accepted I can create a pull request.

[martindsouza]

@janihur I think this is already covered in oos_util_string.truncate_string https://github.com/OraOpenSource/oos-utils/blob/master/source/packages/oos_util_string.pkb#L111-L183 based on #5 Please take a look and let me know if it's a duplicate request.

[janihur]

Yes, I checked that subprogram too but I think the intended use is very different. oos_string_util.truncate_string is fine when you have two clearly different strings with incompatible length restrictions:

declare
  v_str1 constant varchar2(10) := 'Foo is bar';
  v_str2 constant varchar2(9) := oos_string_util.truncate_string(v_str1, 9);
begin
  -- v_str2 value is 'Foo is...'
  null;
end;

But the point of suggested safe append is more to be a "safe" version of:

declare
  v_str varchar2(5);
begin
  for i in 1 .. 10
  loop
    -- will throw ORA-06502 when i = 6
    v_str := v_str || 'A';
  end loop;
end;
/

A trade-off between information "accuracy" and development/code clarify for the cases where the information loss is acceptable.

[martindsouza]

@janihur I'm still a bit confused. I think it may be best to either do a PR with code changes or post the snippet here.

[janihur]

declare
  procedure append(
    p_buf         in out nocopy varchar2
   ,p_str         in varchar2
   ,p_buf_max_len in number default 32767
  ) is
    numeric_or_value_ex exception;
    pragma exception_init(numeric_or_value_ex, -6502);
  begin
    p_buf := p_buf || p_str;
  exception
    when numeric_or_value_ex then
      declare
        v_buf_len constant pls_integer := lengthb(p_buf);
        v_available constant pls_integer := p_buf_max_len - v_buf_len;
        -- magic number lengthb('...') = 3
      begin
        if p_buf_max_len <= 0
        then
          return;
        elsif p_buf_max_len <= 3
        then
          p_buf := substrb('...', 1, p_buf_max_len);
          return;
        end if;

        if v_available < 3
        then
          p_buf := substrb(p_buf, 1, p_buf_max_len - 3);
          p_buf := p_buf || '...';
        else
          p_buf := p_buf || substrb(p_str, 1, v_available - 3) || '...';
        end if;
      end;
  end;
begin
  declare
    v_buf varchar2(4);
  begin
    for i in 1 .. 10
    loop
      append(v_buf, 'XXX', 4);
    end loop;
    dbms_output.put_line(lengthb(v_buf) || ': ' || v_buf);
  end;

  declare
    v_buf varchar2(20);
  begin
    for i in 1 .. 10
    loop
      append(v_buf, 'XXX', 20);
    end loop;
    dbms_output.put_line(lengthb(v_buf) || ': ' || v_buf);
  end;

  declare
    v_buf varchar2(20);
  begin
    for i in 1 .. 10
    loop
      append(v_buf, 'XXX', 30);
      -- you lie (30 <> 20), me throw
    end loop;
    dbms_output.put_line(lengthb(v_buf) || ': ' || v_buf);
  end;

end;
/