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

Main menu

Post navigation

Sociable PL/SQL – associative arrays indexed by VARCHAR2

PL/SQL tables are a really good way of array processing in PL/SQL. In 99% of cases, using a table indexed by PLS_INTEGER works a treat, but what about those times when life would be that bit easier if you could index by VARCHAR2 ?

I was recently presented with one such instance, courtesy of some unconventional table design by now long-departed wise-men.
These gurus had decided to use a synthetic key on a reference table…except the natural key was still in the table….but the actual primary key was half of the natural key plus the synthetic key.

Oh how I would’ve loved to meet these ancients and received the benefit of their wisdom, with the aid of a spot-light, some gaffer tape…and a very sharp stick.

This is a theme I will return to in more detail in a future post ( yep, the 10 things that really wind me up post, coming after the quick ‘n’ dirty tuning post I promised a couple of months ago).

Anyway, back to the PL/SQL table madness.

In this example, we have a table called COUNTRIES – not very original, but hey, the pub’s open so we haven’t got time to hang around.

Now, we have a situation where we want to use the country code for three of those countries, we know which three we want to get. But we then want to use them programatically elsewhere without having to faff about working out which order they are in in our array….

--
-- associate.sql
-- Script to demonstrate a PL/SQL associative array
-- indexed by something other than a number.
--
set serveroutput on
DECLARE
TYPE typ_country_codes IS TABLE OF countries.code%TYPE
INDEX BY countries.country%TYPE;
tbl_country_codes typ_country_codes;
--
-- In this instance, we want all the English speaking
-- countries... except for the USA
-- To paraphrase the colonel in Apocalypse Now -
-- "Yankee don't play cricket !"
--
CURSOR c_countries IS
SELECT code, country
FROM countries
WHERE country in ('AUSTRALIA', 'NEW ZEALAND',
'UNITED KINGDOM');
BEGIN
FOR r_countries IN c_countries LOOP
--
-- assign the code to the table using the value of
-- the country column as the index
--
tbl_country_codes(r_countries.country) :=
r_countries.code;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'Country code for Australia is '
||tbl_country_codes('AUSTRALIA'));
DBMS_OUTPUT.PUT_LINE( 'Country code for New Zealand is '
||tbl_country_codes('NEW ZEALAND'));
DBMS_OUTPUT.PUT_LINE( 'Country code for United Kingdom is '
||tbl_country_codes('UNITED KINGDOM'));
END;
/

And that’s stumps.
Please let me know if you find a less tortuous circumstance in which you find this useful.