Some collection methods I’d like to see added to PL/SQL

In building a code generator I found the need to write a number of helper methods for doing basic modifications of arrays that are indexed by integer – such as appending one array onto another, inserting, and shifting. These arrays represent an ordered sequence of strings (e.g. lines of source code).

I think these would be a useful addition to the language if they were made native – e.g. (new commands in UPPERCASE):

    type str_array_type is table of varchar2(32767)
        index by binary_integer;
    l_lines str_array_type;
    l_new str_array_type;
    l_idx binary_integer;
    .. (some code to fill the arrays here) ..

    -- get a subset of lines
    l_new := l_lines.SLICE(50, 59);

    -- extend l_lines with l_new at the end:

    -- shift l_lines forwards and insert l_new
    -- at the beginning:

    -- insert l_new into l_lines at the given index;
    -- shift any existing lines at that location
    -- forward:
    l_lines.INSERT(l_new, at_idx => 21);

    -- remove the given range of indices from
    -- l_lines, replace with whatever is in l_new:
        from_idx => 120,
        to_idx   => 149);

    -- apply the given substitution on each line
        old_val => 'foo',
        new_val => 'bar');

    -- shift the given range of lines by the given
    -- offset (raise exception if existing data
    -- would get overwritten):
        from_idx => 20,
        to_idx   => 29,
        offset   => 1000);

    -- shift and renumber all indices in the array
    -- with the given starting index and increment:
    l_lines.RENUMBER(start_idx => 10, increment => 10);

    -- make the array contiguous (i.e. remove gaps):

    -- loop over every line in the array that contains
    -- the given string:
    l_idx := l_lines.FIND_NEXT(contains => 'hello');
        exit when l_idx is null;
        .. do something with l_lines(l_idx) ..
        l_idx := l_lines.FIND_NEXT(contains => 'hello',
            from_idx => l_idx);
    end loop;

I’ve illustrated these with a little sample package that may be viewed here:

What do you think?

ReportMap Release 1.5


  1. I’ve not personally tried it but would the MULTISET operators help with the append / prepend requirements?

    Theres also some new stuff related to collections coming in 21c that look quite useful:

    • Hi Alastair, that stuff is pretty cool, definitely. The MULTISET operators deal with the values themselves so are not so useful for my use case.

      The new qualified expression enhancements look promising to simplify a number of the operations I’m talking about.

Leave a Reply

Your email address will not be published / Required fields are marked *