Convert String to Array / List in PL/SQL

CREATE OR REPLACE PACKAGE parse
/*
 Generalized delimited string parsing package

 Author: Steven Feuerstein, steven@stevenfeuerstein.com

 Latest version always available on PL/SQL Obsession:

 www.ToadWorld.com/SF

 Click on "Trainings, Seminars and Presentations" and
 then download the demo.zip file.

 Modification History
 Date Change
 10-APR-2009 Add support for nested list variations

 Notes:
 * This package does not validate correct use of delimiters.
 It assumes valid construction of lists.
 * Import the Q##PARSE.qut file into an installation of
 Quest Code Tester 1.8.3 or higher in order to run
 the regression test for this package.

*/
IS
 SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);

 /*
 Each of the collection types below correspond to (are returned by)
 one of the parse functions.

 items_tt - a simple list of strings
 nested_items_tt - a list of lists of strings
 named_nested_items_tt - a list of named lists of strings

 This last type also demonstrates the power and elegance of string-indexed
 collections. The name of the list of elements is the index value for
 the "outer" collection.
 */
 TYPE items_tt IS TABLE OF maxvarchar2_t
 INDEX BY PLS_INTEGER;

 TYPE nested_items_tt IS TABLE OF items_tt
 INDEX BY PLS_INTEGER;

 TYPE named_nested_items_tt IS TABLE OF items_tt
 INDEX BY maxvarchar2_t;

 /*
 Parse lists with a single delimiter.
 Example: a,b,c,d

 Here is an example of using this function:

 DECLARE
 l_list parse.items_tt;
 BEGIN
 l_list := parse.string_to_list ('a,b,c,d', ',');
 END;
 */
 FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
 RETURN items_tt;

 /*
 Parse lists with nested delimiters.
 Example: a,b,c,d|1,2,3|x,y,z

 Here is an example of using this function:

 DECLARE
 l_list parse.nested_items_tt;
 BEGIN
 l_list := parse.string_to_list ('a,b,c,d|1,2,3,4', '|', ',');
 END;
 */
 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN nested_items_tt;

 /*
 Parse named lists with nested delimiters.
 Example: letters:a,b,c,d|numbers:1,2,3|names:steven,george

 Here is an example of using this function:

 DECLARE
 l_list parse.named_nested_items_tt;
 BEGIN
 l_list := parse.string_to_list ('letters:a,b,c,d|numbers:1,2,3,4', '|', ':', ',');
 END;
 */
 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN named_nested_items_tt;

 PROCEDURE display_list (string_in IN VARCHAR2, delim_in IN VARCHAR2 := ',');

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 );

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 );

 PROCEDURE show_variations;

 /* Helper function for automated testing */
 FUNCTION nested_eq (
 list1_in IN items_tt,
 list2_in IN items_tt,
 nulls_eq_in IN BOOLEAN
 )
 RETURN BOOLEAN;
END parse;
/

CREATE OR REPLACE PACKAGE BODY parse
IS
 FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
 RETURN items_tt
 IS
 c_end_of_list CONSTANT PLS_INTEGER := -99;
 l_item maxvarchar2_t;
 l_startloc PLS_INTEGER := 1;
 items_out items_tt;

 PROCEDURE add_item (item_in IN VARCHAR2)
 IS
 BEGIN
 IF item_in = delim_in
 THEN
 /* We don't put delimiters into the collection. */
 NULL;
 ELSE
 items_out (items_out.COUNT + 1) := item_in;
 END IF;
 END;

 PROCEDURE get_next_item (
 string_in IN VARCHAR2,
 start_location_io IN OUT PLS_INTEGER,
 item_out OUT VARCHAR2
 )
 IS
 l_loc PLS_INTEGER;
 BEGIN
 l_loc := INSTR (string_in, delim_in, start_location_io);

 IF l_loc = start_location_io
 THEN
 /* A null item (two consecutive delimiters) */
 item_out := NULL;
 ELSIF l_loc = 0
 THEN
 /* We are at the last item in the list. */
 item_out := SUBSTR (string_in, start_location_io);
 ELSE
 /* Extract the element between the two positions. */
 item_out :=
 SUBSTR (string_in, start_location_io, l_loc - start_location_io);
 END IF;

 IF l_loc = 0
 THEN
 /* If the delimiter was not found, send back indication
 that we are at the end of the list. */
 start_location_io := c_end_of_list;
 ELSE
 /* Move the starting point for the INSTR search forward. */
 start_location_io := l_loc + 1;
 END IF;
 END get_next_item;
 BEGIN
 IF string_in IS NULL OR delim_in IS NULL
 THEN
 /* Nothing to do except pass back the empty collection. */
 NULL;
 ELSE
 LOOP
 get_next_item (string_in, l_startloc, l_item);
 add_item (l_item);
 EXIT WHEN l_startloc = c_end_of_list;
 END LOOP;
 END IF;

 RETURN items_out;
 END string_to_list;

 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN nested_items_tt
 IS
 l_elements items_tt;
 l_return nested_items_tt;
 BEGIN
 /* Separate out the different lists. */
 l_elements := string_to_list (string_in, outer_delim_in);

 /* For each list, parse out the separate items
 and add them to the end of the list of items
 for that list. */
 FOR indx IN 1 .. l_elements.COUNT
 LOOP
 l_return (l_return.COUNT + 1) :=
 string_to_list (l_elements (indx), inner_delim_in);
 END LOOP;

 RETURN l_return;
 END string_to_list;

 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN named_nested_items_tt
 IS
 c_name_position CONSTANT PLS_INTEGER := 1;
 c_items_position CONSTANT PLS_INTEGER := 2;
 l_elements items_tt;
 l_name_and_values items_tt;
 l_return named_nested_items_tt;
 BEGIN
 /* Separate out the different lists. */
 l_elements := string_to_list (string_in, outer_delim_in);

 FOR indx IN 1 .. l_elements.COUNT
 LOOP
 /* Extract the name and the list of items that go with
 the name. This collection always has just two elements:
 index 1 - the name
 index 2 - the list of values
 */
 l_name_and_values := string_to_list (l_elements (indx), name_delim_in);
 /*
 Use the name as the index value for this list.
 */
 l_return (l_name_and_values (c_name_position)) :=
 string_to_list (l_name_and_values (c_items_position), inner_delim_in);
 END LOOP;

 RETURN l_return;
 END string_to_list;

 PROCEDURE display_list (string_in IN VARCHAR2, delim_in IN VARCHAR2 := ',')
 IS
 l_items items_tt;
 BEGIN
 DBMS_OUTPUT.put_line ('Parse "' || string_in || '" using "' || delim_in
 || '"'
 );
 l_items := string_to_list (string_in, delim_in);

 FOR indx IN 1 .. l_items.COUNT
 LOOP
 DBMS_OUTPUT.put_line ('> ' || indx || ' = ' || l_items (indx));
 END LOOP;
 END display_list;

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 IS
 l_items nested_items_tt;
 BEGIN
 DBMS_OUTPUT.put_line ( 'Parse "'
 || string_in
 || '" using "'
 || outer_delim_in
 || '-'
 || inner_delim_in
 || '"'
 );
 l_items := string_to_list (string_in, outer_delim_in, inner_delim_in);

 FOR outer_index IN 1 .. l_items.COUNT
 LOOP
 DBMS_OUTPUT.put_line ( 'List '
 || outer_index
 || ' contains '
 || l_items (outer_index).COUNT
 || ' elements'
 );

 FOR inner_index IN 1 .. l_items (outer_index).COUNT
 LOOP
 DBMS_OUTPUT.put_line ( '> Value '
 || inner_index
 || ' = '
 || l_items (outer_index) (inner_index)
 );
 END LOOP;
 END LOOP;
 END display_list;

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 IS
 l_items named_nested_items_tt;
 l_index maxvarchar2_t;
 BEGIN
 DBMS_OUTPUT.put_line ( 'Parse "'
 || string_in
 || '" using "'
 || outer_delim_in
 || '-'
 || name_delim_in
 || '-'
 || inner_delim_in
 || '"'
 );
 l_items :=
 string_to_list (string_in, outer_delim_in, name_delim_in,
 inner_delim_in);
 l_index := l_items.FIRST;

 WHILE (l_index IS NOT NULL)
 LOOP
 DBMS_OUTPUT.put_line ( 'List "'
 || l_index
 || '" contains '
 || l_items (l_index).COUNT
 || ' elements'
 );

 FOR inner_index IN 1 .. l_items (l_index).COUNT
 LOOP
 DBMS_OUTPUT.put_line ( '> Value '
 || inner_index
 || ' = '
 || l_items (l_index) (inner_index)
 );
 END LOOP;

 l_index := l_items.NEXT (l_index);
 END LOOP;
 END display_list;

 PROCEDURE show_variations
 IS
 PROCEDURE show_header (title_in IN VARCHAR2)
 IS
 BEGIN
 DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
 DBMS_OUTPUT.put_line (title_in);
 DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
 END show_header;
 BEGIN
 show_header ('Single Delimiter Lists');
 display_list ('a,b,c');
 display_list ('a;b;c', ';');
 display_list ('a,,b,c');
 display_list (',,b,c,,');
 show_header ('Nested Lists');
 display_list ('a,b,c,d|1,2,3|x,y,z', '|', ',');
 show_header ('Named, Nested Lists');
 display_list ('letters:a,b,c,d|numbers:1,2,3|names:steven,george',
 '|',
 ':',
 ','
 );
 END;

 FUNCTION nested_eq (
 list1_in IN items_tt,
 list2_in IN items_tt,
 nulls_eq_in IN BOOLEAN
 )
 RETURN BOOLEAN
 IS
 l_return BOOLEAN := list1_in.COUNT = list2_in.COUNT;
 l_index PLS_INTEGER := 1;
 BEGIN
 WHILE (l_return AND l_index IS NOT NULL)
 LOOP
 l_return := list1_in (l_index) = list2_in (l_index);
 l_index := list1_in.NEXT (l_index);
 END LOOP;

 RETURN l_return;
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 RETURN FALSE;
 END nested_eq;
END;
/

Oracle Predefined PL/SQL Exceptions

Exception ORA Error SQLCODE Raise When …
ACCESS_INTO_NULL 06530 -6530 A program attempts to assign values to the attributes of an uninitialized object
CASE_NOT_FOUND 06592 -6592 None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN 06511 -6511 A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX 00001 -1 A program attempts to store duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR 01001 -1001 A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 n a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED 01017 -1017 A program attempts to log on to Oracle with an invalid username or password.
NO_DATA_FOUND 01403 +100 A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.
NOT_LOGGED_ON 01012 -1012 A program issues a database call without being connected to Oracle.
PROGRAM_ERROR 06501 -6501 PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL 30625 -30625 A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.
STORAGE_ERROR 06500 -6500 PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT 06533 -6533 A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT 06532 -6532 A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID 01410 -1410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE 00051 -51 A time out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS 01422 -1422 A SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE 01476 -1476 A program attempts to divide a number by zero.

source : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm