PL/SQL Associative Arrays and why too much Rugby is bad for you

Once upon a time, working on an IT project in a large organisation meant reams of documentation, tangles of red-tape, and then burning the candle at both ends to get everything finished on time.
Then, someone discovered this Agile thing that dispensed with all of that.
The upshot ? Well, the documentation has been reduced ( although the red-tape is still problematic).
Many large organisations now adopt an approach that is almost – but not completely – unlike SCRUM.
It is at the business end of such a project I now find myself…burning the candle at both ends.
To pile on the excitement, Milton Keynes’ own Welsh enclave has become increasingly voiciferous in recent days.
The only SCRUM Deb is interested in is that of the Welsh Rugby Team grinding remorselessly over English bodies come Sunday.
She keeps giving me oh-so-subtle reminders of the result of last year’s game, such as when picking lottery numbers :
“Hmmm, three is supposed to be lucky…but not if your English. How about THIRTY !”
“But you’re married to an Englishman”, I pointed out during one of her more nationalistic moments.
“Which makes him half Welsh !”, came the retort.
At this point, I decided that discretion was the better part of logic and let the matter drop.
As a result of all this frenzied activity and feverish atmosphere, sometimes I’ve not been quite at the top of my game.
One particularly embarassing mishap occured late one evening and involved PL/SQL Tables – or Associative Arrays as they’re called these days – and the dreaded ORA-06531: Reference to uninitialized collection.

This particular post therefore, is mainly a reminder to myself of how to initialize and (just as importantly) clear down a Collection to prevent mysterious missing or, just as problematic, additional, records ( as well as less mysterious runtime errors).

The Insanity Check

At some point, it would be nice to go away somewhere for a weekend of culture (which may or may not include extensive sampling of local alcoholic beverages).
I’ve got a table containing a list of possible destinations…and whether or not they are located in a Rugby playing nation :

CREATE TABLE getaway_plans(
    city_name VARCHAR2(50),
    country_name VARCHAR2(50),
    play_rugby VARCHAR2(1)
)
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('VIENNA', 'AUSTRIA', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('ROME', 'ITALY', 'Y')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('AUCKLAND', 'NEW ZEALAND', 'Y')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('CARDIFF', 'WALES', 'Y')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('BERLIN', 'GERMANY', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('WARSAW', 'POLAND', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('HAVANA', 'CUBA', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('COPENHAGEN', 'DENMARK', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('BRUGES', 'BELGIUM', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('AMSTERDAM', 'NETHERLANDS', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('BASEL', 'SWITZERLAND', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('PERTH', 'AUSTRALIA', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('NEW YORK', 'USA', 'Y')
/

COMMIT;

OK – so a “weekend away” would probably require me finally ironing out the bugs in my lottery number picker program ( it keeps selecting the wrong six numbers).

Using those whizzy associative arrays, I shall now attempt to come up with a shortlist…of places where Rugby is not widely popular.
I will concede that this could be done with far less hassle using a simple select statement. That would, however, defeat the object of the exercise…

CREATE TABLE shortlist_cities
(
    city_name VARCHAR2(50),
    country_name VARCHAR2(50)
)
/

This should do the trick…

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    -- associatve array based on the cursor    
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    -- and another one based on the target table.
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE;
    
    tbl_all_cities typ_all_cities;

    tbl_short typ_shortlist;
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
    END LOOP;
    CLOSE c_getaway;
END;
/

…Kick it off and…

DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 23


SQL> 

Yep, our first stumbling block. the TBL_SHORT collection has not been initialized.
Now at this point you may be a bit stumped as there is no obvious way to initialize this collection.
The other collection in the program – TBL_ALL_CITIES seems to have been automatically initialised by the FETCH.
We’ll come back to this in a bit.
In the meantime though, just how do you initialize an Associative Array without pointing a cursor at it ?
The refreshingly simple answer is : add an index clause to the type declaration …

set serveroutput on
DECLARE
    TYPE typ_funny_shaped_balls IS TABLE OF shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_rugby typ_funny_shaped_balls;
BEGIN
    DBMS_OUTPUT.PUT_LINE('There are '||tbl_rugby.COUNT||' elements in the collection.');
END;
/

Run this and we get…

There are 0 elements in the collection.

PL/SQL procedure successfully completed.

SQL> 

No nasty error when referencing the collection.
So, applying this to our code, we should be good to go, right ?

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;

    tbl_short typ_shortlist;

    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
    END LOOP;
    CLOSE c_getaway;
END;
/

Run this and all seems well…

SQL> @madness2
Bulk Collect Loop Iteration
Bulk Collect Loop Iteration
Bulk Collect Loop Iteration
Bulk Collect Loop Iteration

PL/SQL procedure successfully completed.

SQL> 

…until you look at the resulting data…

SQL> select city_name from shortlist_cities order by 1;

CITY_NAME
--------------------------------------------------
AMSTERDAM
AMSTERDAM
BASEL
BASEL
BERLIN
BRUGES
BRUGES
COPENHAGEN
HAVANA
PERTH
PERTH

CITY_NAME
--------------------------------------------------
VIENNA
WARSAW

13 rows selected.

Hmmm…not quite what we had in mind.
The problem here is that neither array is being cleared down between loop iterations.
The results are…interesting.

Let’s try and address that…

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;
    tbl_short typ_shortlist;

    tbl_ac_empty typ_all_cities;
    tbl_short_empty typ_shortlist;
    
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
        -- clear down the arrays between each iteration.
        tbl_all_cities := tbl_ac_empty;
        tbl_short := tbl_short_empty;
    END LOOP;
    CLOSE c_getaway;
END;
/

Here, we’ve simply assigned the arrays to an empty array of the same type at the bottom of the loop.
This seems to solve the problem…

SQL> select city_name from shortlist_cities order by 1;

CITY_NAME
--------------------------------------------------
AMSTERDAM
BASEL
BERLIN
BRUGES
COPENHAGEN
HAVANA
PERTH
VIENNA
WARSAW

9 rows selected.

SQL> 

At this point, you might think that there’s still a potential issue lurking in this code. OK, we’ve initialised the tbl_short array by including the INDEX BY clause in the type definition.
We haven’t done this for the tbl_all_cities array. OK, at the moment this array is intialised by the fetch from the cursor. What would happen if the cursor fetch didn’t return any rows …

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans
        WHERE play_rugby = 'X';
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;
    tbl_short typ_shortlist;

    tbl_ac_empty typ_all_cities;
    tbl_short_empty typ_shortlist;
    
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
        tbl_all_cities := tbl_ac_empty;
        tbl_short := tbl_short_empty;
    END LOOP;
    CLOSE c_getaway;
END;
/

…er, it still works. Yep, as long as the Associative Array is the target of a fetch before you reference it, it will be initialised.

One final point to note. Whilst it’s common practice to cleardown your Associative Arrays by simply assigning them the value of an empty array of the same type, there is another way…

set serveroutput on
DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;
    tbl_short typ_shortlist;
    
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
        -- clear down the arrays between each iteration.
        DBMS_OUTPUT.PUT_LINE('Currently '||tbl_all_cities.COUNT||' records in tbl_all_cities.');
        DBMS_OUTPUT.PUT_LINE('Currently '||tbl_short.COUNT||' records in tbl_short.');
        tbl_all_cities.DELETE;
        tbl_short.DELETE;
        DBMS_OUTPUT.PUT_LINE('Now tbl_all_cities contains '||tbl_all_cities.COUNT||' records.');
        DBMS_OUTPUT.PUT_LINE('...and tbl_short contains '||tbl_short.COUNT||' records.');
    END LOOP;
    CLOSE c_getaway;
END;
/

Simply using the DELETE method will cleardown our arrays…

Bulk Collect Loop Iteration
Currently 4 records in tbl_all_cities.
Currently 1 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.
Bulk Collect Loop Iteration
Currently 4 records in tbl_all_cities.
Currently 4 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.
Bulk Collect Loop Iteration
Currently 4 records in tbl_all_cities.
Currently 4 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.
Bulk Collect Loop Iteration
Currently 1 records in tbl_all_cities.
Currently 0 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.

PL/SQL procedure successfully completed.

SQL> 

Right, six-pack of Brain’s bitter.Check. Inflatable Daffodil. Check. Deb humming Land of My Fathers, Check. The game must be about to start…

About these ads

4 thoughts on “PL/SQL Associative Arrays and why too much Rugby is bad for you

      • Sayan,

        the short answer is because I wanted to explore the features (and pitfalls) of PL/SQL Associative Arrays.
        “But they’re not Associative Arrays” I hear you cry. Have a look at my response to your other comment :)

        Mike

    • Sayan,

      thanks for the comments. Interesting stuff !

      To deal with your points in order :

      You may have a point about what Oracle are calling these objects ( this week anyway).
      However, elsewhere in the documentation you find http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/tdddg_subprograms.htm#TDDDG242. It seems to be a fairly fine distinction between Nested Tables and Associative Arrays. Besides I believe that Nested Tables is also the term that is (was) applied to VARRAYS.

      As for using EXTEND. Yes you can. However, adding the INDEX BY clause means that you don’t need to mess about with EXTEND. I’m not sure if this makes any material difference to performance (i.e. having to extend before adding each record) but my personal preference is to sort this out in the declaration of the type so that I know I don’t have to worry about it thereafter.

      Your other point regarding initialization is interesting …

      set serveroutput on
      DECLARE
      TYPE typ_shortlist is table of shortlist_cities%ROWTYPE;
      tbl_short typ_shortlist;
      BEGIN
      tbl_short := typ_shortlist();
      dbms_output.put_line(‘tbl_short ‘||tbl_short.COUNT||’ records.’);
      END;
      /

      Run this and we get…

      tbl_short 0 records.

      PL/SQL procedure successfully completed.

      SQL>

      That’s good to know. Thanks for the tip.

      Regards,

      Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s