aspose file tools*
The moose likes JDBC and the fly likes Reading values from dbms_sql.varchar2_table in a Oracle SP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reading values from dbms_sql.varchar2_table in a Oracle SP" Watch "Reading values from dbms_sql.varchar2_table in a Oracle SP" New topic
Author

Reading values from dbms_sql.varchar2_table in a Oracle SP

Monty Guppy
Ranch Hand

Joined: Sep 15, 2001
Posts: 49
I have the following simple stored procedure which returns the first and middle names of all people having a given last_name. Since there could be multile people with the same last name, there could be 0,1, or more records returned. Can someone please help me with this. I guess my biggest challenge is to be able to appropriately use registerOutParameter().
Thanks.
----------------------------------------------
CREATE OR REPLACE
PROCEDURE PRC_SEL_TEST
(in_last_name IN VARCHAR2,
out_first_name OUT dbms_sql.varchar2_table,
out_middle_name OUT dbms_sql.varchar2_table
)
IS
i integer := 0;
cursor c1 is
select legal_first_name,legal_middle_name
from person
WHERE legal_last_name= in_last_name;
BEGIN
for person_rec in c1 loop
i:= i+1;
out_first_name(i) := person_rec.legal_first_name;
out_middle_name(i) := person_rec.legal_middle_name;
end loop;
END;
/
 
Don't get me started about those stupid light bulbs.
 
subject: Reading values from dbms_sql.varchar2_table in a Oracle SP