selecting from a returned cursor...

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Mon, 2 Feb 2004 18:27:00 +0100
Message-ID: <bvm195$gq3$1_at_nntp.fujitsu-siemens.com>

Hi!
[Quoted] [Quoted] 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 Received on Mon Feb 02 2004 - 18:27:00 CET