Anton heralded by Steven Feuerstein as very creative PL/SQL developer

Steven Feuerstein, one of the world’s leading PL/SQL experts, runs a series of column on OTN (Oracle Technology Network). One of the repeating elements of his column is the PL/SQL Challenge. This is a problem or puzzle that PL/SQL Developers can sharped their skills on. One of the recent challenges proved to be very difficult and the number of reactions Feuerstein received was rather small. However, one of the top PL/SQL Developers at AMIS, Anton Scheffer, sent in his solution. Feuerstein:

I received a smaller number of solutions for this puzzle than usual.[…]. In any case, they were still interesting and useful submissions!

I must admit that even I was challenged by other elements of this puzzle: For example, how to handle different character sets and the many potential numeric formats. I certainly didn’t feel like I had the time to do much research on this issue. And analyzing a string to see if it is a valid PL/SQL identifier may not be an overwhelming task, but it’s also not trivial.

Imagine my sigh of relief and gratitude, then, for Anton Scheffer! Anton relied on the TO_NUMBER built-in to offer some creative solutions to these other requirements, namely:

• To check to see if a string is a valid identifier, try to use it in a dynamic PL/SQL block. If the block compiles, the string must be a valid identifier.
• Anton defines a letter as a character that has different representations for UPPER and lower. That is, the upper-cased and lower-cased forms of a delimiter or number are equal. Those of a letter are different.

That makes a lot of sense to me. It may not apply to all character sets, however; I encourage you to send me an email if you have any insights on this matter or alternative solutions.

I took Anton’s implementation, enhanced it by adding the ability to pass in a numeric format mask so that the check for “is it a number” can be adjusted to handle various unusual specifications, refactored some of the code (for example, I changed FOR loops to WHILE loops), and of course reformatted it in my favorite style!

See PL/SQL Challenge: String Theory.

The challenge by the way was:

My application code needs to determine if a string has one of the following characteristics:

• Is a number
• Contains only letters
• Contains letters and numbers
• Is a valid Oracle identifier.

How would you implement these requirements? And in particular, how might you handle issues such as different character sets, different decimals, thousands of delimiters within a string representing a number, and so on?