Advanced Oracle PL/SQL Programming with Packages

10.1. PLVprs: Useful String Parsing Extensions

The PLVprs (PL/Vision PaRSe) package offers a set of procedures and functions that provide generic and very flexible string-parsing functionality. These programs extend the builtin string functions of PL/SQL. They can:

• Parse a string into its atomics

• Count the number of atomics in a string

• Count the frequency of a substring within a string

• Return the n th atomic in a string

• Wrap a long line of text into a paragraph

• Display a wrapped line of text

You can use PLVprs within other packages and programs to analyze strings and display their contents. It is also used within PL/Vision by PLVvu and PLVdyn to display long messages.

All of our applications require manipulation of textual information. I have often encountered the need to parse and analyze those strings in order to answer requests like the following (and I am sure that you could add more to the list):

• Count the number of words in a string.

• Separate out all words and punctuation in a string into separate components.

• Return the nth value in a semicolon-delimited string. This is a very common situation in Oracle Forms applications, in which a developer might pack a set of values into a global variable like this: "123;5555;6623.11;".

10.1.1. Developing a General Solution

Taken separately, it is not too hard to develop a solution to any of the items on this list. If you build solutions to each individual requirement on a case-by-case basis, you will end up with an enormous volume of redundant ...