Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS

From: Tim X <timx_at_nospam.dev.null>
Date: Wed, 22 Jun 2011 08:07:32 +1000
Message-ID: <874o3ig7ob.fsf_at_puma.rapttech.com.au>

Andreas Mosmann <mosmann_at_expires-30-06-2011.news-group.org> writes:

> 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

This is not working becauswe you are mixing up the PL/SQl layer and the SQL layer.

Strongly suggest you read the Oracle introductory material on SQL and PL/SQL, especially the sections dealing with calling PL/SQl funcitons from SQL.

The key point to note is that SQL, on the whole, does not understand SQL data types. If you want SQL to understand the values returned from a function, you need to make sure tht function is returning ONLY SQL datatypes.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Tue Jun 21 2011 - 17:07:32 CDT