Re: selecting from a returned cursor...

Volker Hetzer wrote:

> Hi!
> I'm in the process of trying out the different ways a function can return data to
> sql in a "select"able form.
>
> So far I've figured out how to do that with nested tables:
> ---------------------------------------------------------------------------------
> --the trick is to declare that type outside the package.
> create type ZULCHECK_ComponentListType as table of varchar2(255);
> /
> show err;
>
> create package ZulCheck_Beispiel authid current_user
> as
> function get_result_from_collection return ZULCHECK_ComponentListType;
> end ZulCheck_Beispiel;
> /
> show err;
>
> create package body ZulCheck_Beispiel
> as
> ComponentList ZULCHECK_ComponentListType;
> function get_result_from_collection return ZULCHECK_ComponentListType
> is
> begin
> return ComponentList;
> end get_result_from_collection;
> end ZulCheck_Beispiel;
> /
> show err;
> ----------------------------------------------------------------------------------
> In which case
> select * from TABLE(ZulCheck_Beispiel.get_result_from_collection);
> works perfectly fine.
>
>
>
> Also, I can declare a function returning a ref cursor.
> -----------------------------------------------------------------------------------
> create package ZulCheck_Beispiel authid current_user
> as
> type testcursor is REF CURSOR;
> function get_result_from_cursor return testcursor;
> end ZulCheck_Beispiel;
> /
> show err;
>
> create package body ZulCheck_Beispiel
> as
> function get_result_from_cursor return testcursor
> is
> res1 testcursor;
> begin
> open res1 for select * from ZULCHECK_ZULASSUNG;
> return res1;
> end get_result_from_cursor;
>
> end ZulCheck_Beispiel;
> /
> show err;
> -----------------------------------------------------------------------------------
> this:
> select * from cursor(ZulCheck_Beispiel.get_result_from_cursor);
> doesn't work.
>
> So, is there any way to select from a returned cursor?
>
> Lots of Greetings!
> Volker

You left out pipelined table functions.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)