It's truly one of the oddities of the PL/SQL language that it does not offer a delimited string parsing program. The closest we can get is DBMS_UTILITY.COMMA_TO_TABLE, and that is sadly deficient (it only parses comma-delimited strings and each item between the commas must be a valid PL/SQL identifier).
So I built one myself (the parse package) and put it in the
demo.zip file that accompanies my training materials. You will find it in the parse.pkg file. For the past few years it has consisted of a single function that accepts a delimited list and returns a collection of the items in the list.
On a flight from London Heathrow to Istanbul in April 2009, though, I decided to make this package a bit more useful. I added two overloadings of the string_to_list function so that you can now easily parse (1) a string that contains multiple lists and (2) a string that contains multiple lists, each of which has a name.
So I have updated the demo.zip download to include this new version, and I thought I would bring it to your attention. The demo.zip contains about 2000 files and there is no index. So it's just a tad bit difficult to find the useful programs within it.
I have also created a
Quest Code Tester for Oracle test definition to verify the behavior of these functions. If you would like to check that out, install Quest Code Tester 1.8.3 or higher (available as a beta
here until mid-May 2009), and then import the Q#PARSE.qut file (also in demo.zip).
Here are examples of using the three different overloadings of parse.string_to_list:
DECLARE
l_list parse.items_tt;
BEGIN
l_list:=parse.string_to_list('a,b,c,d',',');
END;
/
DECLARE
l_list parse.nested_items_tt;
BEGIN
l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/
DECLARE
l_list parse.named_nested_items_tt;
BEGIN
l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/
The third overloading, which returns a collection of type parse.named_nested_items_tt, also offers a demonstration of the elegance and usefulness of multi-level and string-indexed collections.
I hope you find the parse package useful.
And I hope that someday we can happily deprecate it, for the very good reason that PL/SQL finally offers one of its own.