ALTER SESSION SET plsql_ccflags = 'max_varchar2_length:32767'
/

CREATE OR REPLACE PACKAGE string_fun
IS
   SUBTYPE maxvarchar2_t IS VARCHAR2 ( $$max_varchar2_length );

   TYPE maxvarchar2_aat IS TABLE OF maxvarchar2_t
      INDEX BY PLS_INTEGER;

   TYPE clob_aat IS TABLE OF CLOB
      INDEX BY PLS_INTEGER;

   FUNCTION betwnstr (
      string_in      IN   VARCHAR2
    , start_in       IN   PLS_INTEGER
    , end_in         IN   PLS_INTEGER
    , inclusive_in   IN   BOOLEAN := TRUE
   )
      RETURN VARCHAR2;

   FUNCTION list_to_collection (
      string_in   IN   VARCHAR2
    , delim_in    IN   VARCHAR2 DEFAULT ','
   )
      RETURN maxvarchar2_aat;

   FUNCTION cloblist_to_collection (
      string_in   IN   CLOB
    , delim_in    IN   VARCHAR2 DEFAULT ','
   )
      RETURN clob_aat;
END string_fun;
/

CREATE OR REPLACE PACKAGE BODY string_fun
IS
   FUNCTION betwnstr (
      string_in      IN   VARCHAR2
    , start_in       IN   PLS_INTEGER
    , end_in         IN   PLS_INTEGER
    , inclusive_in   IN   BOOLEAN := TRUE
   )
      RETURN VARCHAR2
   IS
      c_last CONSTANT PLS_INTEGER := LENGTH ( string_in );
      l_start PLS_INTEGER;
      l_numchars PLS_INTEGER
          := LEAST ( ABS ( end_in ), c_last ) - ABS ( start_in )
                          + 1;
   BEGIN
      IF    string_in IS NULL
         OR ( start_in < 0 AND end_in > 0 )
         OR ( start_in > 0 AND end_in < 0 )
         OR ( start_in < 0 AND end_in > start_in )
         OR ( start_in > 0 AND end_in < start_in )
      THEN
         RETURN NULL;
      ELSE
         IF start_in < 0
         THEN
            l_start := GREATEST ( end_in, -1 * LENGTH ( string_in ));
         ELSIF start_in = 0
         THEN
            l_start := 1;
            l_numchars := ABS ( end_in ) - ABS ( l_start ) + 1;
         ELSE
            l_start := start_in;
         END IF;

         IF NOT NVL ( inclusive_in, FALSE )
         THEN
            l_start := l_start + 1;
            l_numchars := l_numchars - 2;
         END IF;

         RETURN ( SUBSTR ( string_in, l_start, l_numchars ));
      END IF;
   END betwnstr;

   FUNCTION cloblist_to_collection (
      string_in   IN   CLOB
    , delim_in    IN   VARCHAR2 DEFAULT ','
   )
      RETURN clob_aat
   IS
      l_loc PLS_INTEGER;
      l_row PLS_INTEGER := 1;
      l_startloc PLS_INTEGER := 1;
      l_return clob_aat;
   BEGIN
      IF string_in IS NOT NULL
      THEN
         LOOP
            -- Get the next item.
            l_loc := INSTR ( string_in, delim_in, l_startloc );

            IF l_loc = l_startloc
            THEN
               l_return ( l_row ) := NULL;
            ELSIF l_loc = 0
            THEN
               l_return ( l_row ) := SUBSTR ( string_in, l_startloc );
            ELSE
               l_return ( l_row ) :=
                  SUBSTR ( string_in, l_startloc, l_loc - l_startloc );
            END IF;

            -- Was that the last one?
            IF l_loc = 0
            THEN
               EXIT;
            ELSE
               l_startloc := l_loc + 1;
               l_row := l_row + 1;
            END IF;
         END LOOP;
      END IF;

      RETURN l_return;
   END cloblist_to_collection;

   FUNCTION list_to_collection (
      string_in   IN   VARCHAR2
    , delim_in    IN   VARCHAR2 DEFAULT ','
   )
      RETURN maxvarchar2_aat
   IS
      l_loc PLS_INTEGER;
      l_row PLS_INTEGER := 1;
      l_startloc PLS_INTEGER := 1;
      l_return maxvarchar2_aat;
   BEGIN
      IF string_in IS NOT NULL
      THEN
         LOOP
            -- Get the next item.
            l_loc := INSTR ( string_in, delim_in, l_startloc );

            IF l_loc = l_startloc
            THEN
               l_return ( l_row ) := NULL;
            ELSIF l_loc = 0
            THEN
               l_return ( l_row ) := SUBSTR ( string_in, l_startloc );
            ELSE
               l_return ( l_row ) :=
                  SUBSTR ( string_in, l_startloc, l_loc - l_startloc );
            END IF;

            -- Was that the last one?
            IF l_loc = 0
            THEN
               EXIT;
            ELSE
               l_startloc := l_loc + 1;
               l_row := l_row + 1;
            END IF;
         END LOOP;
      END IF;

      RETURN l_return;
   END list_to_collection;   
END string_fun;
/