Re: selecting from a returned cursor...

"Daniel Morgan" <damorgan_at_x.washington.edu> schrieb im Newsbeitrag news:1075751073.301548_at_yasure...
> 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.
All right, I did indeed. I'll have a look at them.

Lots of Greetings and thanks!
Volker Received on Mon Feb 02 2004 - 22:20:46 CET