Re: PL/SQL Arrays

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 1999 09:32:51 +0100
Message-ID: <930130809.14033.0.nnrp-03.9e984b29@news.demon.co.uk>

The code is doing exactly as expected.
The line:

            v_UserID := int_arr(rec_user.UserID); means:

            overwrite the array v_user_id with the array constructed from
            the single element rec_user.userid

What you want to do is:
            extend the array v_userid by appending an item equal to
            the value rec_user.userid.

To do this you need to:
            make the input array an empty array as the procedure starts
            for each row returned
                extend the array
                put the latest returned row in at the last position

Sample code:

create or replace procedure get_ints (

    v_int in out int_arr
)
as

    cursor c1 is select pct_free from user_tables; begin

     v_int := int_arr();

     for r1 in c1 loop
          v_int.extend;
          v_int(v_int.last) := r1.pct_free;
     end loop;

end;
/

BTW - this is not a very efficient use of arrays and cursors. It may be that you have to use this strategy, but there is probably a more efficient way of doing the job (e.g. with casting, or pl/sql array processing).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

jeperkins4_at_my-deja.com wrote in message <7kp424$mfm$1_at_nnrp1.deja.com>...
>While inserting into an Oracle User Defined type array of table and
>calling the constructor method to insert values into that array, the
>data appears to overwrite the previously inserted value so that there
>is only one "record" in the array.
>
>create or replace type IDENT_ARR as table of varchar(30);
>create or replace type INT_ARR as table of integer;
>
>
>
> BEGIN
>
> for rec_user in cur_user loop
> v_UserID := int_arr(rec_user.UserID);
> end loop;
> END;
>
>Why is it that when I call "getUser" I only get one record back;
>although there are multiple records in the database?
>
Received on Wed Jun 23 1999 - 03:32:51 CDT