• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Reading values from dbms_sql.varchar2_table in a Oracle SP

 
Monty Guppy
Ranch Hand
Posts: 49
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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;
/
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic