Re: Arrays in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/12
Message-ID: <33f061c1.34048198@newshost>#1/1

On Mon, 11 Aug 1997 15:11:24 -0600, ualwayn_at_usgs.gov wrote:

>Hi,
>
>Can anyone please tell me how to declare and access a two-dimensional
>array in PL/SQL?
>
>Any help will be appreciated.
>
>Thanks,
>
>Una.
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

You can't do a direct 2 dimensional array but in 7.3 (7.3 and up) you can have tables of records that allow you to achieve the same effect. The following example demonstrates and Nx3 matrix where N is not known until run time but the '3' is fixed at compile time (its how many components I have in my record).

It also demonstrates the new attributes added to pl/sql tables in 7.3 such as .COUNT.... create or replace package types
as

    type rec is record
    (

        a   number,
        b   number,
        c   number

    );
    type two_dim is table of rec index by binary_integer;  

    procedure dump_it;
end;
/  

create or replace package body types
as  

    multi_array two_dim;  

procedure dump_it
is
begin  

    for i in 1 .. multi_array.count loop

        dbms_output.put_line( multi_array(i).a || ',' ||
                              multi_array(i).b || ',' ||
                              multi_array(i).c );

    end loop;
end dump_it;    

begin

    for x in ( select user_id, to_char(created,'j') c, rownum from all_users )     loop

        exit when ( x.rownum > 50 );
        multi_array(x.rownum).a := x.user_id;
        multi_array(x.rownum).b := x.c;
        multi_array(x.rownum).c := x.rownum;

    end loop;
end;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 12 1997 - 00:00:00 CDT