PL/SQL Arrays

From: <jeperkins4_at_my-deja.com>
Date: Tue, 22 Jun 1999 22:51:53 GMT
Message-ID: <7kp424$mfm$1@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;

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?

Any help is appreciated...

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 22 1999 - 17:51:53 CDT