## 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**PL/SQL tables can growindefinitely, you don’t need to declare in advance how big they are.*unbounded*:**They are**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.*sparse*:

### 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.

October 9th, 2009 at 6:56 pm

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

June 22nd, 2010 at 11:43 pm

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

June 22nd, 2010 at 11:47 pm

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

June 22nd, 2010 at 11:54 pm

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

June 22nd, 2010 at 11:57 pm

create or replace

FUNCTION read_mytable (

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