Oracle – for when it was like that when you got there

Main menu

Post navigation

The PL/SQL Associative Array – the path to untold riches

Having given the matter some thought, I’ve concluded that there are two ways to fame and fortune.
The first of these is talent. For the benefit of my Colombian readership ( hello German) :
I can’t play football like Faustinio Asprilla; I can’t drive as fast as Juan Pablo Montoya; and as for Carlos Valdarama’s hair…well mine deserted me some time ago. I do have something in common with Shakira – my hips don’t lie. Unfortunately, what they say is “this waistline is the result of too many nights in the pub”.

The second way is winning the lottery. OK, so the fame thing is a bit tenuous, but from the outside looking in, I’d say it was overrated. So, never mind the fame, quiero solo mucho dinero ( I just want loads of cash) !
All of which brings me to the point. I was knocking the rust off my Java the other day, by writing a program to generate lottery numbers. Then it struck me, I’d never written a lottery program in PL/SQL. Should be fairly straightforward – generate 6 random numbers between 1 and 49, make sure that you don’t have any duplicates, then print them out in order.
It’s that last bit that’s a little problematic. After all, PL/SQL is primarily designed to write to a table and so ordering the result set is usually done by simply querying the table with an ORDER BY clause.
That’s OK most of the time, but I don’t really need a table for this program, I just need an array.
So how do you get an array of numbers to display in ascending order when you haven’t got something like the Arrays.sort method available in Java ?
The simplest way is to use one of those incredibly useful associative arrays.

Because the array index is the same as the value of the array element, we’re able to get PL/SQL to loop through the array in ascending order. This makes it so much easier to fill in the lottery form.
Now all I need to do is sit back and work out which tropical island I’m going to buy with the winnings. A foolproof and cunning plan. I ask you, what could possibly go wrong ?