When Gary was first hired at an aircraft component repair company, one of his first duties was to add functionality to their Oracle 11i based application that would "automatically add mods" to the system.

As Gary soon learned, a "mod" referred to the work that needed to be performed on particular piece of equipment for maintenance or repair. Because the company specialized in a fairly specific type of components (multimedia), the steps to perform the work were fairly consistent: 1) Remove any certifications or other affixed notices; 2) Remove field-serviceable access panels 3) Remove factory-serviceable panels; ...; 18) Replace joints with new silicone glue; etc. This worked out well as, due to federal regulations, all modification steps taken needed to be recorded in the equipment's recertification file.

To represent this in their application, a user would enter a comma-separated list of numbers in the "Related Modification Steps" field. However, since a piece of equipment could have anywhere from a handful to several dozen mods done to it, the field accepted "shorthand" input to represent a range of numbers. For example, entering "1-6,8" would represent the same steps as "1,2,3,4,5,6,8". Upon saving the repair order, the "mods" string would then be used to add data to a linking table associating the repair order with the pre-defined repair steps.

What a great first assignment! Gary thought as he went searching for the value set responsible for validating the user's input. He figured that the developer must have written a spiffy little PL/SQL function that would parse the string of numbers, detecting one or more ranges of numbers and take into account the occasional "one off" digit that would not be included in any range.

However, one question lingered for Gary. Why was he assigned to write functionality to "auto-add" mod sequences when they would be auto-converted from what the user inputted in the first place?

"Well," Gary's supervisor explained, "every now and then you come across a mod level that hasn't been entered. This of course means emailing one of three designated employees to add it, which means the process of logging equipment out has to be put on hold. This can be a problem if one these employees are out of the office, or they just simply skip over the email."

"Okaaaay," Gary said, quizzically raised an eyebrow. He still wasn't sure exactly what his boss was referring to.

"You'll win friends for this one!" cheered his supervisor.

When Gary had a chance to dig in to the code, he found a clue that answered exactly he was supposed to do. It was the monster-sized table named ALL_MODS:

Oracle Apps 11i is teh funzorz.

Instead of parsing the "mods" string, the original developers created a table that would validate and then convert the user's input in to the appropriate string of values. If the user's input wasn't in the table, he'd have to ask one of the "specialists" to add the row.

The good news was that Gary would have an opportunity to write a spiffy little PL/SQL function that would parse the string of numbers. And of course, the bad news was that this was just the tip of the iceberg.