It happens quite frequently that in our database applications – in queries or in PL/SQL packages – we need to use the values from more or less standard domains or custom lists of values. For example the list of all months in the year – to show a breakdown list of some measure per month. Or the days of the week, to do something similar. Sometimes we just need a list of numbers in a certain range. And it is quite common to outer join such a collection of domain values with the set of measurements, using the partition outer join feature – to ensure that for each category of measurements, there is an aggregate for each of the values in the domain.

This article demonstrates several examples of standard domains, some SQL tricks for composing domains out of thin air and a number of features to spell out numbers and internationalize days and month names.

(more…)