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.

9 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
    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

  6. New healthy man Says:

    Just desired to say I am just relieved that i happened upon your web site! http://bit.ly/2f0xJ92

  7. prindia.in Says:

    If you would like to get a good deal from this post then you have to apply
    such techniques to your won web site.

  8. posicionamiento web Precios Says:

    Estaba buscando esa informacion hace ciclo, te lo agradezco, estoy de contrato con tu punto de vista y grano
    igual. Despues de buscar mucho por Internet encontre lo que buscaba.
    Genial!!! muchas gracias

  9. blo traffic Says:

    Simple Products Addition – There a simple setting options
    available at Word – Press development to assist you add and customize your products.
    Did you learn to salsa, go to a skate park, go ahead and take kids sledding.
    You can capture both screen images and video and is a credit application that sits on your desk top.

Leave a Comment