Defining a two-dimensional array of numbers

Question:
How can I define a two-dimensional array of numbers in PL/SQL?
Answer:
This question was answered in the January/February 2006 issue of Oracle Magazine.
Although PL/SQL does not natively support the declaration and manipulation of multidimensional arrays, you can emulate these structures using nested collection definitions, which were first supported in Oracle9i Database Release 1.
Here is a brief example to get you started and introduce you to some of the challenges you may encounter as you use collections in this way.
First, create a collection of associative arrays.
CREATE OR REPLACE PACKAGE twodim_aa
IS
TYPE data_t IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;

TYPE array_t IS TABLE OF data_t
INDEX BY PLS_INTEGER;
END twodim_aa;
/
The first, inner collection—data_t—contains the data for each cell in the two-dimensional array. Each row in the outer collection—array_t—contains a collection of the first type.
Now declare a variable based on that outer collection type —array_t—, which will serve as a two-dimensional array. In the following script, I declare such a collection—
DECLARE
l_2d_grid twodim_aa.array_t;
—and then assign values to three cells: (1,1), (1,2), and (200,206). Notice that the syntax is different from that used in traditional array cell specification, namely: (1)(1), (1)(2), and (200)(206). Also, since I am using associative arrays to define my two-dimensional array, I do not have to specify a size for this two-dimensional array.
DECLARE
l_2d_grid twodim_aa.array_t;
BEGIN
l_2d_grid (1) (1) := 100;
l_2d_grid (1) (2) := 120;
l_2d_grid (200) (206) := 200;

IF l_2d_grid (1)(2) < 100
THEN
...
END IF;
END;
/
If you want to define a two-dimensional array with a specific size, you should use VARRAYs, because when you declare a VARRAY you must also specify a maximum number of elements allowed in the VARRAY. Here, for example, is the specification of a 100x100 element array:
CREATE OR REPLACE PACKAGE
twodim_varray
IS
TYPE data_t IS
VARRAY (100) OF NUMBER;
TYPE array_t IS
VARRAY (100) OF data_t;
END twodim_varray;
/
Populating this array becomes very complicated, however, because before I can assign a value to an index in the array, that array must be initialized and then extended (the same will be true of nested tables). To do this, I must now expose the stark reality that the twodim_aa.array_t is not really a two-dimensional array—rather, it is a collection of collections. I must first extend one dimension. That dimension, a VARRAY, defines an element that will in turn hold a VARRAY (the inner collection—array_t). Before I can assign a value to that index, however, I must initialize the inner collection and extend it. Finally, I can assign a value.
DECLARE
l_2d_grid twodim_varray.array_t
:= twodim_varray.array_t();
BEGIN
l_2d_grid.EXTEND;
l_2d_grid(1)
:= twodim_varray.data_t();
l_2d_grid(1).EXTEND;
l_2d_grid (1) (1) := 100;
END;
/
Because of these complications, you will almost certainly want to either
• Stick with associative arrays. It will greatly simplify your life when it comes to manipulating the values in multidimensional arrays.
• Build an encapsulation to hide the code required to manipulate a VARRAY-based multidimensional array.
Regardless of which collection type you choose to emulate the multidimensional array, you will need to write your own programs to perform high-level operations on your array.
Finally, if you need to perform advanced operations on vectors and matrices, you might also consider using the UTL_NLA package, new to Oracle Database 10g Release 2. This package exposes within PL/SQL a subset of the BLAS (Basic Linear Algebra Subprograms) and LAPACK (Linear Algebra PACKage) Version 3.0 operations on vectors and matrices. These structures are represented as VARRAYs in UTL_NLA.