Oracle PLSQL string tokenizer package procedure to break a string into tokens based on a delimiter

--Following package procedure is used to break a string into substrings or tokens based on a delimiter

CREATE PACKAGE BODY xx_token_pkg
 AS   TYPE token_tbl IS TABLE OF VARCHAR2 (2000)
INDEX BY BINARY_INTEGER;

PROCEDURE string_tokenizer (
                                      p_string        IN        VARCHAR2,
                                      p_delimiter    IN        VARCHAR2,
                                      p_result_tbl   OUT     xx_ar_billing_ext_pkg.token_tbl
                                           )
   IS

      CURSOR c_tokens
      IS
         SELECT     LEVEL,
                    SUBSTR (string_to_tokenize,
                            DECODE (LEVEL,
                                    1, 1,
                                      INSTR (string_to_tokenize,
                                             delimiter,
                                             1,
                                             LEVEL - 1
                                            )
                                    + 1
                                   ),
                              INSTR (string_to_tokenize, delimiter, 1, LEVEL)
                            - DECODE (LEVEL,
                                      1, 1,
                                        INSTR (string_to_tokenize,
                                               delimiter,
                                               1,
                                               LEVEL - 1
                                              )
                                      + 1
                                     )
                           ) token
               FROM (SELECT p_string || p_delimiter AS string_to_tokenize,
                            p_delimiter AS delimiter
                       FROM DUAL)
         CONNECT BY INSTR (string_to_tokenize, delimiter, 1, LEVEL) > 0
         ORDER BY LEVEL ASC;

      l_index   BINARY_INTEGER := 0;

   BEGIN

      FOR token_rec IN c_tokens

      LOOP
         p_result_tbl (l_index) := token_rec.token;
         l_index := l_index + 1;
      END LOOP;

   EXCEPTION

     WHEN OTHERS THEN
       print_log('Exception in string_tokenizer'||sqlerrm);
   END string_tokenizer;

END xx_token_pkg;

--Following is an anonymous block which calls xx_token_pkg.string_tokenizer to break the string into substrings/tokens using a separater/delimiter '-'DECLARE

   l_result   xx_token_pkg.token_tbl;

BEGIN

   xx_token_pkg.string_tokenizer ('Test1-Test2-Test3', '-', l_result);

   FOR i IN l_result.FIRST .. l_result.LAST

   LOOP

      DBMS_OUTPUT.put_line ('result=' || l_result (i));

   END LOOP;

END;

Running the above blcok results in the following output:-

result=Test1
result=Test2
result=Test3