Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: 2D Array of integers
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.
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;
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