## Simulating a Two-Dimensional Array in PL/SQL

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):

x y Combined Index
1 1 1001
12 34 12034
10 999 10999

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.

### 5 Responses to “Simulating a Two-Dimensional Array in PL/SQL”

1. Oscar Says:

mytabtype IS TABLE OF NUMBER INDEXED BY BINARY_INTEGER;
*
ERROR at line 7:
ORA-06550: l�nea 7, columna 14:
PLS-00103: Encountered the symbol “IS” when expecting one of the following:
constant exception
table long double ref
char

2. Philippe Says:

create or replace
TYPE MYTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Error: PLS-00355: use of pl/sql table not allowed in this context

3. Philippe Says:

create or replace
PROCEDURE write_mytable(
x IN NUMBER,
y IN NUMBER,
array_value IN NUMBER) IS
mytabtype IS TABLE OF NUMBER INDEXED BY BINARY_INTEGER;
c_multipler CONSTANT NUMBER :=1000;

prec_cep_calculations mytabtype;
BEGIN
prec_cep_calculations ((x * c_multipler) + y) := array_value;
END;

Error PLS-00103: Encountered the symbol “IS” when expecting one of the following: constant exception table long double ref char timestamp interval date binary notional character nchar

4. Philippe Says:

create or replace
PROCEDURE write_mytable(
x IN NUMBER,
y IN NUMBER,
array_value IN NUMBER) IS
TYPE mytabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
c_multipler CONSTANT NUMBER :=1000;
prec_cep_calculations mytabtype;

BEGIN
prec_cep_calculations ((x * c_multipler) + y) := array_value;
END;

No compile errors

5. Philippe Says:

create or replace
x IN NUMBER,
y IN NUMBER) RETURN NUMBER IS array_value NUMBER;
c_multipler CONSTANT NUMBER :=1000;
TYPE mytabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
prec_cep_calculations mytabtype;

BEGIN
array_value:= prec_cep_calculations((x * c_multipler)+ y);
RETURN array_value;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

no compile errors — SQL Developer version 1.5.5