Monday, April 20, 2009

Need to parse a delimited string? Use this package!

For several years now, anyone who was interested could download my "demo.zip" file, which consists of some 2000 files that I use in my trainings. This zip contains demonstration scripts, but also some generic utilities, like the parse package, which parsed a delimited string and returned a collection of the individual elements.

I recently enhanced the package to allow me to easily parse lists of lists, and even named lists of lists. Rather than just "bury it" inside the demo.zip, I thought I would tell you about it on my blog and give you a direct link to the code.

My objective is to reduce the possibility that any other PL/SQL developers will ever need or want to take the time to build their own parsing utility. Oracle PL/SQL should, of course, provide such a program for us. Sadly, it does not. Or, rather, it offers DBMS_UTILITY.COMMA_TO_TABLE, which is so eccentric and limited in its functionality that I find it embarrassing.

So....the parse package offers three different overloadings of the string_to_list function:

PACKAGE parse
IS
SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);
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;

FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
RETURN items_tt;

FUNCTION string_to_list (string_in IN VARCHAR2
, outer_delim_in IN VARCHAR2
, inner_delim_in IN VARCHAR2
)
RETURN nested_items_tt;

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;
END parse;


Here are examples of using each one:

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 ('letters:a,b,c,d|numbers:1,2,3,4', '|', ':', ',');
END;


The package also contains helper programs to display the contents of lists and compare collection records for equality. This latter function is utilized by Quest Code Tester to automatically test the package. The zip file contains a Code Tester export file (.qut) that you can import into Code Tester 1.8.3 or higher and verify the behavior of these programs. Currently, 1.8.3 is in beta and you can download it here.

I hope you find them useful! If, of course, you find a bug, please let me know so I can fix it. Even better, add the test case that reproduces the bug to the Code Tester test definition, then fix it yourself and send me all of the modified files.

That would be a really friendly thing to do. :-)

2 comments:

Don Burleson said...

Hi Steve,

>> My objective is to reduce the possibility that any other PL/SQL developers will ever need or want to take the time to build their own parsing utility.

Great idea!

Somebody should offer a library of reusable PL/SQL functions . . .

Waleed Mohsen said...

thanks for this article
it's good for me