Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-FFM2.ecrc.net!newsfeed1.sbs.de!news.pdb.sbs.de!nntp.fujitsu-siemens.com!not-for-mail
From: "Volker Hetzer" <volker.hetzer@ieee.org>
Newsgroups: comp.databases.oracle.tools
Subject: Re: selecting from a returned cursor...
Date: Mon, 2 Feb 2004 22:20:46 +0100
Organization: Fujitsu Siemens Computers GmbH
Lines: 78
Message-ID: <bvmeve$npf$1@nntp.fujitsu-siemens.com>
References: <bvm195$gq3$1@nntp.fujitsu-siemens.com> <1075751073.301548@yasure>
NNTP-Posting-Host: abg0752c.abg.fsc.net
Mime-Version: 1.0
Content-Type: text/plain;
 charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Trace: nntp.fujitsu-siemens.com 1075756846 24367 141.29.58.180 (2 Feb 2004 21:20:46 GMT)
X-Complaints-To: abuse@sbs.de
NNTP-Posting-Date: Mon, 2 Feb 2004 21:20:46 +0000 (UTC)
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Xref: newssvr20.news.prodigy.com comp.databases.oracle.tools:84583


"Daniel Morgan" <damorgan@x.washington.edu> schrieb im Newsbeitrag news:1075751073.301548@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