Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS

From: Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org>
Date: Tue, 21 Jun 2011 08:19:43 +0200
Message-ID: <1308637183.61_at_user.newsoffice.de>

Andreas Mosmann schrieb am 16.06.2011 in <1308210796.89_at_user.newsoffice.de>:

Special thanks to Gunther Herrmann, he gave me the answers, I searched for.
The following example is only to demonstrate, how to write a stored procedure (function), that returns many rows. I would prefer to stuff it into a package, but I found no working way and so I did it like the following:

--create a rowtype definition for the wished table
create type T_RETURN_LINE as object ( ID number, Datum Date, Text varchar2(256));

--create a table definition

create type T_RETURN_TABLE is table of T_RETURN_LINE;

--a simple function that returns some useless Rows with some useless
Content
create function ReturnManyRows(InputFrom in number,InputTo in number) return T_RETURN_TABLE PIPELINED is

	i number;
	MyRow T_RETURN_LINE;
begin
	for i in InputFrom..InputTo loop
		MyRow:=T_RETURN_LINE(NULL,sysdate,'HELLO');
		MyRow.ID:=i;
		PIPE ROW (MyRow);
	end loop;
	return;

end;

--The select- statement

select * from table(ReturnManyRows(1,5));

Maybe anyone can use it.

Andreas Mosmann

--

wenn email, dann AndreasMosmann <bei> web <punkt> de Received on Tue Jun 21 2011 - 01:19:43 CDT