Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS

From: Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org>
Date: Mon, 20 Jun 2011 16:04:04 +0200
Message-ID: <1308578644.84_at_user.newsoffice.de>

Gunter Herrmann schrieb am 17.06.2011 in <4dfb6062$0$6630$9b4e6d93_at_newsspool2.arcor-online.net>:

> Hi Andreas!

> Andreas Mosmann wrote:

>> If Possible it would be nice to do something like
>> 
>> select * from MyProc('A','B','C')

> What about

> select * from table(MyFunction('A','B','C'))

> MyFunction then will be a table function (for a small amount of data only,
> otherwise
> you might run out of memory), for more rows you can use a pipelined table
> function
> (introduced in Oracle 9i). This one returns rows before the execution is
> finished.

> You will need to create database types for the row type of the function.
> Additionally
> you will create a table type as TABLE OF <row type from above>.

> Hope that helps

This would be great, but I did not get it working. I get ora-00902.

My source (simplified)
create package MyPack as

********************************************************/
type T_R_RETURN is record (
	  ID number, Datum Date, Operation varchar(256), Zahl1 number, Zahl2 

number);

type T_REF_TABLE is table of T_R_RETURN index by binary_integer;

function MyFunc(param1 in char,param2 in char) return MyPack.T_REF_TABLE;

end;

create package body MyPack as

function MyFunc(param1 in char,param2 in char) return MyPack.T_REF_TABLE is
i number :=1;
ResultTable T_REF_TABLE;
begin

	i:=2;
	for i in 1..10 loop
		ResultTable(i).ID:=i;
	end loop;
	return ResultTable;

end;

end;

select * from table(MyPack.MyFunc('1','2')); -- returns ORA-00902

> Gunter in Orlando, Florida

Thank you
Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Mon Jun 20 2011 - 09:04:04 CDT