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) !
Continue reading

PL/SQL Arrays – The Autumn Collection

I’ve spent some time recently playing with PL/SQL arrays in the context of uploading from flat-files.
In the course of this, it struck me that PL/SQL arrays come in a variety of shapes and sizes ( or in this case, small, medium and large).
So, if Sir – or Madam – would care to step into the fitting room, we’ll see if we can find something to suit.
Continue reading

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.

CREATE TABLE countries(
code VARCHAR2(3),
country VARCHAR2(50))
/

The COUNTRY table has the following records :-


SQL> select * from countries;
CODE COUNTRY
---- -----------------------
AUS AUSTRALIA
BEL BELGIUM
FRA FRANCE
GBR UNITED KINGDOM
GER GERMANY
JPN JAPAN
ITA ITALY
USA UNITED STATES
NZL NEW ZEALAND


9 rows selected.

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.