Re: PL/SQL Arrays

>
> >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
>

Thanks for the answer! How could I pass this "bulk collection" over to Java via 8i/SQLJ via a PL/SQL Stored procedure?

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Jul 03 1999 - 17:14:04 CDT