Re: PL/SQL Arrays

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 23 Jun 1999 11:27:33 GMT
Message-ID: <3771c41a.174132679@newshost.us.oracle.com>

A copy of this was sent to jeperkins4_at_my-deja.com (if that email address didn't require changing) On Tue, 22 Jun 1999 22:51:53 GMT, you wrote:

>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;
>
>
>PROCEDURE getUser
> (v_UserID out int_arr,
> v_AddressID out int_arr,
> v_UserName out ident_arr,
> v_FirstName out ident_arr,
> v_LastName out ident_arr,
> v_UserType out ident_arr,
> v_addr1 out ident_arr,
> v_addr2 out ident_arr,
> v_City out ident_arr,
> v_StateID out int_arr,
> v_CountryID out int_arr,
> v_zip out ident_arr,
> v_email out ident_arr) is
>
> cursor cur_user is
> select UserID, AddressID, UserName, FirstName, LastName,
> UserType, Street1, Street2, City, StateID, Zip,
> CountryID, Email, LastUpdated
> from vUsers;
>
> rec_user cur_user%rowtype;
>
> BEGIN
>
> for rec_user in cur_user loop
> v_UserID := int_arr(rec_user.UserID);
> v_AddressID := int_arr(rec_user.AddressID);
> v_UserName := ident_arr(rec_user.UserName);
> v_FirstName := ident_arr(rec_user.FirstName);
> v_LastName := ident_arr(rec_user.LastName);
> v_UserType := ident_arr(rec_user.UserType);
> v_addr1 := ident_arr(rec_user.street1);
> v_addr2 := ident_arr(rec_user.street2);
> v_City := ident_arr(rec_user.City);
> v_StateID := int_arr(rec_user.StateID);
> v_Zip := ident_arr(rec_user.zip);
> v_CountryID := int_arr(rec_user.CountryID);
> v_email := ident_arr(rec_user.email);
> 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?
>

In addition to the information provided by Jonathan -- you could simply your code massively by doing the following:

SQL> create or replace type myScalarType as object

  2  ( ename      varchar2(30),
  3    empno      number,
  4    mgr        number

  5 )
  6 /
Type created.

SQL> create or replace type myTableType as table of myScalarType;   2 /
Type created.

SQL> create or replace procedure get( p_data out myTableType )   2 is
  3 begin

  4      select cast(multiset(select ename, empno, mgr from emp)
  5                  AS myTableType)
  6        into p_data
  7        from dual;

  8 end;
  9 /
Procedure created.

Instead of passing column by colum to get, create a scalar type that represents your cursor and pass a table of that type. We can fill that up in one fell swoop (in oracle8i, release 8.1 we can 'bulk collect' into the individual tables as well but the above method works in 8.0 and up).

To use this then you would:

SQL> declare
  2 l_data myTableType;
  3 begin
  4 get( l_data );
  5

  5      for i in 1 .. l_data.count loop
  6          dbms_output.put_line( l_data(i).ename );
  7      end loop;

  8 end;
  9 /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER PL/SQL procedure successfully completed.

>Any help is appreciated...
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 23 1999 - 06:27:33 CDT