January's PL/SQL Puzzler

From the PL/SQL Pipeline
(http://www.revealnet.com/plsql-pipeline/index.htm). Solve this month's
Puzzler and your name will be entered into a drawing for a free technical book from O'Reilly & Associates (http://www.ora.com)

January's PL/SQL Puzzler

Steven Feuerstein writes:

Consider the Print dialogue in Microsoft Word; in the paper range box, you can specify All, Current Page or specific Pages.

The puzzle this month is to build a PL/SQL utility (a.k.a, API, a.k.a., package) that might be used by Microsoft (if Bill Gates was not so hung up about using Basic or Java or C or C++, instead of the substantially more robust Oracle PL/SQLlanguage) to take your entry for Pages and translate that string into a list of integer values.

Does this seem frivolous? Not so! I ran into a need for it just the other day. I continue to fine-tune PL/Generator, a code generator for PL/SQL that RevealNet will release in the first quarter of 1998. With PL/Generator, you set up templates based on one or more template files. A template file has a sequence number. The standard template for Table Encapsulation (wrapping a package around a table) in PL/Generator consists of these files:

(PUZZLE SPECS OMITTED TO SAVE BANDWIDTH)
There are several aspects to consider for this puzzle:

1. What is the best approach to take for the interface? In other words, how do you most effectively and easily (for the user) communicate back the integer values identified by the string? I have decided to not include a package specification in the puzzle. I believe that the ability to construct this interface aspect of our applications is a skill many of us need to improve upon.
2. How do you implement the code behind the interface (package body) for maximum clarity, minimal code and optimal performance?
3. It would be awfully nice to allow the user to specify the range delimiter (overriding the default of "-") and the page separator
   (overriding the default of ","). With this flexibility, you could
   conceivably provide a string like "7*15*49:56" and it would be interpreted the same as "7,15,49-56".

Some hints:

•If you have access to PL/Vision Professional, I suggest you take a look at the PLVseg package. •If you receive the Oracle Developer newsletter from Pinnacle, I suggest you take a look at the May 1997 issue (or get in touch with Pinnacle at www.pinpub.com.

Consider using a PL/SQL table (index table in Oracle8-speak) to communicate your list of integers back to the user.

Best wishes,

Cam White
RevealNet, Inc. Received on Tue Jan 13 1998 - 00:00:00 CST