Re: PlL/SQL and Array Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Jun 1998 13:20:00 GMT
Message-ID: <3582d5ee.3662226@192.86.155.100>

A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan) (if that email address didn't require changing) On 10 Jun 98 16:01:17 GMT, you wrote:

> I am trying to pass an array into a stored function on an Oracle 7.3.4 server.
> First, can I pass an array to a function?
>
>Second, is there any command/syntax for looping through all the items in the array?
> For example,... FOR EACH ITEM IN ARRAY blah,blah, blah... or is there a count function to
> find the number of items in the array so I can do a LOOP that way?
>
>Thanks,
>Sean Dolan

You don't mention the client. Lets say initially this is pl/sql calling pl/sql. Then, it might look like:

create or replace package types
as

    type vcArray is table of varchar2(255) index by binary_integer; end;
/

• demo routines ------------------------------------- create table test_msg( seq number, msg varchar2(72) );

create or replace procedure show_array( p_array in types.vcArray ) as

    l_index number;
    l_cnt number default 0;
begin

    for i in 1 .. p_array.count loop

        insert into test_msg values ( l_cnt, p_array(i) );
        l_cnt := l_cnt+1;

    end loop;

    insert into test_msg values ( l_cnt, '------------------' );

    l_cnt := l_cnt+1;

    l_index := p_array.first;
    loop

        exit when (l_index is NULL);
        insert into test_msg values ( l_cnt, p_array(l_index) );
        l_cnt := l_cnt+1;
        l_index := p_array.next( l_index );

    end loop;
end;
/

create or replace procedure demo_array
as

    l_array types.vcArray;
begin

    for i in 1 .. 10 loop

        l_array(i) := 'This is element ' || i;     end loop;

    show_array( l_array );
end;
/

---

This will show pl/sql sending an array from one procedure to the other:

exec demo_array;
select * from test_msg order by seq;
truncate table test_msg;

Note that in show_array, I showed 2 ways to iterate over an array. If you know the array is 'dense' and starts at one, you can simply loop from 1 .. array.count. If the array is sparse and you aren't sure it starts at 1, you should use the attributes .FIRST and .NEXT to loop over the elements...

Here is a pro*c example passing an array from pro*c to pl/sql. Note that the array will always start at 0 in C as is the custom and that the array will always start at 1 in pl/sql (thats the way pl/sql maps to a C array)...

{
exec sql begin declare section;

    varchar     array[25][25];
    int         array_len;
    int         i;

exec sql arraylen array(array_len);
exec sql end declare section;

    for( i = 0, array_len = 10; i < array_len; i++ )     {

        sprintf( array[i].arr, "This is element %d", i );
        array[i].len = strlen( array[i].arr );

    }

    exec sql whenever sqlerror do sqlerror_hard();

    exec sql execute
    begin

        show_array( :array );
    end;
    end-exec;

}  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 11 1998 - 08:20:00 CDT