Re: 2D Array of integers

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 10 Nov 2004 13:12:02 -0800
Message-ID: <4b5394b2.0411101312.c6d8ba9@posting.google.com>

manuel.deslis_at_mail.com (Manuel DESLIS) wrote in message news:<29f85e9a.0411100307.2c09a1dc_at_posting.google.com>...
> Hi there
>
> I work on a Java application and I try to store in a table (Oracle 9i)
> a matrix of integers, ie a 2D array of integers.
>
> And I can't make it work.
>
> Here si my code :
>
> // I have already done this on my data base :
> // CREATE TYPE VECTOR AS VARRAY(10) OF NUMBER(10, 0)
> // CREATE TYPE MATRIX AS VARRAY(10) OF VECTOR
>
> // create 2 descriptors : vector and matrix
> ArrayDescriptor descVector =
> ArrayDescriptor.createDescriptor("VECTOR", connection);
> ArrayDescriptor descMatrix =
> ArrayDescriptor.createDescriptor("MATRIX", connection);
>
> // create matrix
> double[] line1 = {15, 25, 35};
> ARRAY arrayLine1 = new ARRAY(descVector, connection, line1);
> double[] line2 = {17, 27, 37};
> ARRAY arrayLine2 = new ARRAY(descVector, connection, line2);
> Object matrix[] = {arrayLine1, arrayLine2};
> ARRAY arrayMatrix = new ARRAY(descMatrix, connection, matrix);
>
> // insert the matrix
> PreparedStatement ps = connection.prepareStatement("INSERT INTO
> MY_TABLE VALUES (?)");
> ((OraclePreparedStatement) ps).setARRAY(1, arrayMatrix);
> ps.execute();
>
>
> Unfortunately, this doesn't work.
> Anyone has a better idea ?
>
> Thanks for your help, bye.
>
> Manuel Deslis

A google search for "two dimensional array" brings up this same article.

But here we go again, you can simulate a two dimensional array with a simple mapping function. It's similar to what your compiler does when you use a
multidimensional array in languages that support it. So no need for nested
types and tables.

INDEX2D
GIVEN THE INDICES IN A 2-DIMENSIONAL ARRAY.
THIS RETURNS A UNIQUE INDEX IN A LINEAR ARRAY.
Ed Prochak
Magic Interface, Ltd.
440-498-3700
CREATE OR REPLACE FUNCTION INDEX2D ( A INTEGER, B INTEGER ) RETURN INTEGER AS K INTEGER; N INTEGER; NDX INTEGER; BEGIN K := A+B-1; N := K*(K+1); N := N/2; NDX := N+1-B; RETURN NDX; END; /

The above routine assumes the first entry in the table is location (1,1) in the 2-D array and location 1 in the linear array. It also assumes use of only positive integers. If you prefer your 2-D array to start at location (0,0), then simply add 1 to both parameters before calling this routine OR adjust the body as follows:
BEGIN

    K := A+B+1;
    N := K*(K+1);
    N := N/2;

    NDX := N-B;
    RETURN NDX;
  END; Feature: This is an unlimited 2-D array, well suited for use with PL/SQL tables. So don't forget to check that the entry exists at a given location before retrieving it.

So you use this within your PL/SQL code for example:

DECLARE
    TYPE EJP_TABLE IS TABLE OF VARCHAR2(30)                INDEX BY BINARY_INTEGER;
    LYNEAR EJP_TABLE;
BEGIN
    LYNEAR(2):='TWO'; --- IT REALLY IS A LINEAR ARRAY     LYNEAR(INDEX2D(1,2)):='xxx'; --- but INDEX2D allows you to pretend it's 2D

LYNEAR(INDEX2D(2,2)):='YYY';
LYNEAR(INDEX2D(3,5)):='ZZZ'; --- GREAT FOR SPARSE MATRICES TOO. DBMS_OUTPUT.PUT_LINE('@(2) = '|| LYNEAR(2) ); DBMS_OUTPUT.PUT_LINE('@(1,2)) = '||LYNEAR(INDEX2D(1,2))||

                   ' @'||INDEX2D(1,2) );
    DBMS_OUTPUT.PUT_LINE('@(2,2)) = '||LYNEAR(INDEX2D(2,2))||
                   ' @'||INDEX2D(2,2) );
    DBMS_OUTPUT.PUT_LINE('@(3,5)) = '||LYNEAR(INDEX2D(3,5))||
                   ' @'||INDEX2D(3,5) );

END;
/

There is an obvious limit on using this function, ie you have to use it whenever you use the array.

Not a built in feature of PL/SQL, but very flexible none-the-less.

It can be extended to multiple demensions. You just need to do the math.

HTH
Ed

PS it's all based on a facinating property of integers. Consider: How many positive integers (i) are there? Infinitely many.
Call this infinity I1

How many positive tuples (a,b) are there where a and b are positive integers?
Infinitely many.
call this infinity I2

Now, is infinity I2 greater than infinity I1?

It turns out the answer is NO, they are the same size. And the mapping function INDEX2D demonstrates this. IOW, there isn't a single tuple (a,b) that the function cannot find a matching positive integer (i) value.

So who said number theory was useless? Received on Wed Nov 10 2004 - 15:12:02 CST