In PL/SQL Version 2 Oracle provided developers with the ability to store information in arrays, though they chose to confuse everyone by calling them PL/SQL tables. This was a big step forward, followed by another in PL/SQL 2.3 when they added record types andbuilt-in table operations. However PL/SQL tables have one disadvantage over their counterparts in C or Perl: they can only have one index value.

This page illustrates a method of simulating a multi-dimensional array. The example given below shows a two dimensional array, but the principles can be followed to allow more.

Properties of PL/SQL Tables

PL/SQL tables have a two properties that will help us when constructing an array:

• They are unbounded: PL/SQL tables can growindefinitely, you don’t need to declare in advance how big they are.
• They are sparse: No memory is allocated forempty cells, if you only have mytable(1) and mytable(1000) no memory is allocated to the 999 rows in between.

Method

To squeeze two dimensions into one, multiply one index by some largevalue and add it to the other. The “large value” (hereafter called the multiplier) needs to be larger than any value that will be placed in the second index. For example, if we want to use indices x & y and we know that y will never exceed 999 we could use a multiplier of 1000 to combine them thus (colour coded for clarity):

Of course the y value is no longer unbounded - it can’t exceed 999 - but the sparse nature of PL/SQL tables means that there’s no reason why this upper limit shouldn’t be 99999999 instead: just pick a larger multiplier.

Implementation

This method could be implemented by simple hard coding, thus:

--
-- Saving myvalue as cell (x,y)
--
mytable((x * 1000) + y) := myvalue;
--
-- Reading myvalue from cell (x,y)
--
myvalue := mytable((x * 1000) + y);

However, doing so has its disadvantages:

• If you need to change the multiplier, you have to do so wherever the table is referenced (though you can work round this by using a constant instead).
• Trying to read an empty cell will raise a NO_DATA_FOUND exception.
• If Oracle ever introduce multi-dimensional arrays to PL/SQL you’ll have to change your code everywhere to take advantage.
• It may not be obvious to someone maintaining your code just what is going on.

Instead, I prefer to encapsulate the array handling into two procedures -one to read, one to write. An example is shown below:

DECLARE
   c_multiplier   CONSTANT NUMBER := 1000;
   --
   -- Define a PL/SQL table. Here it's a table of NUMBERs,
   -- but it could be anything.
   --
   mytabtype IS TABLE OF NUMBER INDEXED BY BINARY_INTEGER;
   mytable   mytabtype;
   --
   -- Arbitrary variables used in this example
   --
   xx        NUMBER;
   yy        NUMBER;
   val       NUMBER;

   /*
   ||
   || READ a cell from mytable, returning NULL
   || if the cell is undefined
   */
   FUNCTION read_mytable (x IN NUMBER,
                          y IN NUMBER) RETURN NUMBER IS
      value  NUMBER;
   BEGIN
      value := mytable((x * c_multiplier) + y);
      RETURN value;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN NULL;
   END;
   /*
   ||
   || WRITE a value to a cell of mytable
   ||
   */
   PROCEDURE write_mytable (x     IN NUMBER,
                            y     IN NUMBER,
                            value IN NUMBER) IS
   BEGIN
      mytable((x * c_multiplier) + y) := value;
   END;
   --
BEGIN
   --
   -- ... your program code goes in here ...
   --
   write_mytable (xx,yy,val);
   --
   -- ... and here ...
   --
   val := read_mytable (xx,yy);
END;

Summary

The above approach presents a simple way to implement a two dimensional array structure. Placing the nuts and bolts into read and write procedures means that, once set up, you won’t have to worry about how the array is implemented and can focus on what you’re actually doing with it.

A similar approach, which further encapsulates the pseudo-array into a package, can be found in Oracle PL/SQL Programming by Steven Feuerstein and Bill Pribyl.