set echo on
spool c:\temp\ray_rowset.txt
set doc on

/*
drop function f_ray
/
drop function f_ray2
/
drop function f_ray3
/
drop function f_ray4
/
drop type c_ray
/
drop type o_ray
/
*/

--
-- starting with some simple data
--
select 1 c1,2 c2,3 c3 from dual
/

--
-- create an object type in oracle in 2 steps
-- 1) make a "record" type so to speak (called an OBJECT in Oracle parlance)
-- 2) make a "table" type (called a COLLECTION, which is a set of objects (aka. a set of rows?))
--
create type o_ray is object (a number,b number,c number)
/

create type c_ray is table of o_ray
/

--
-- create a function that returns the collection type
-- notice we are initializing the collection to an empty set
-- not actually necessary in this case because we are using whole object assignment here
-- but if you want to put rows into the collection one at a time you would have to init it first
--
-- then we have a wrapper select gather and convert a set of rows into the collection variable
-- by using special operators that tell Oracle SQL it is collecting a set of rows (multiset)
-- and that these should be converted into "such and such" collection (cast... c_ray)
--
-- we can then return the collection once full
--
-- it should be noted that the collection is housed in memory, so there is a practical limit
-- the to number of rows and row widths that can be manipulated with simple "table" functions
-- like this one
--
create function f_ray return c_ray is
   c_ray_v c_ray := c_ray();
begin
   select cast(multiset(
                        select 1,2,3 from dual
                       ) as c_ray
              )
   into c_ray_v
   from dual
   ;
   return (c_ray_v);
end;
/
show errors

--
-- call the function in a from clause and convert its resulting collection to a set of rows
--
select *
from table(cast(f_ray as c_ray))
/

--
-- as an alternative, make the function pipelined to reduce memory consumption if collection is large
-- or if you want to create a sequence of "piped" functions working together as in some kind of ETL
-- pipelined functions returns rows of the collection one at a time (or groups at a time maybe)
-- thus the only memory needed is that necessary to hold data till the return (normally one row)
-- this is far more memory efficient and when done in series with multiple such functions
-- can lead to excellent parallel processing.  It can be very useful for ETL processes.
--
create function f_ray2 return c_ray pipelined is
   o_ray_v o_ray;
begin
   for r1 in (
              select 1 c1,2 c2,3 c3 from dual
             ) loop
      o_ray_v := o_ray(r1.c1,r1.c2,r1.c3);
      pipe row (o_ray_v );
   end loop;
   return;
end;
/
show errors

--
-- this function is referenced the same way as its non-pipelined version
--
select *
from table(cast(f_ray2 as c_ray))
/

--
-- another thing we can do...
-- we can create a refcursor as the return type of the function
-- but this is more normally done when sending data outside of oracle
-- as in: a result set heading for a java routine
--
create function f_ray3 return sys_refcursor is
   c1 sys_refcursor;
begin
   open c1 for select 1 c1,2 c2,3 c3 from dual;
   return c1;
end;
/
show errors

--
-- unfortunately you cannot treat refcursors like rowsets in SQL
-- because it just won't work: Oracle sql can't select directly from refcursors in the from clause
-- and there are not built in transform functions for such a thing
-- so the following sql will raise an error
--
--from table(cast(f_ray3 as c_ray))
--                *
--ERROR at line 2:
--ORA-00932: inconsistent datatypes: expected - got CURSER
--
--
select *
from table(cast(f_ray3 as c_ray))
/

--
-- you may however reference the refcursor as a column value
-- because it is a scalar comming out of the function
-- it may be a special datatype, but it is still a scalar datatype
--
select f_ray3 c1
from dual
/

--
-- our collections were scalars too
--
select f_ray2 c1
from dual
/


--
-- our collections were scalars too
--
select f_ray c1
from dual
/

--
-- there are also things called cursor expressions
-- these are fast and easy ways to turn sets of rows into scalars
-- very handy for generating XML data
-- and although not shown here, these can be correlated to driving table(s)
-- thus accepting datavalues from the rows of driving table(s) in order to drive their execution
--
select cursor(select 1,2,3 from dual) c1
from dual
/

--
-- jumping outside our simple example for just a moment, here is a correlated cursor expression
-- by examining the results you can see how the col_list scalar (a set or rows in one column value)
-- is correct for the corresponding table
--
select a.table_name,cursor(select column_name from user_tab_columns b where b.table_name = a.table_name) col_list
from user_tables a
/

--
-- ok, back to the original thought
--
-- for more fun, we can combine mechanims so that we have a totally open
-- way of passing any query, executing it, and returning the results back via sql
-- as long as the result set fits the collection type
--
create function f_ray4 (string_p in varchar2) return c_ray pipelined is
   c1 sys_refcursor;
   o_ray_v o_ray;
begin
   open c1 for string_p;
   loop
      o_ray_v := o_ray(null,null,null);
      fetch c1 into o_ray_v.a,o_ray_v.b,o_ray_v.c;
      if c1%notfound then exit; end if;
      pipe row (o_ray_v);
   end loop;
   close c1;
   return;
end;
/
show errors

--
-- looks just like the other stuff only we pass the query into the function call as a string
-- rather than embedding it in the code directly
--
select *
from table(cast(f_ray4('select 1 c1,2 c2,3 c3 from dual') as c_ray))
/

--
-- of course one has to ask if you are doing dynamic sql this way then why not just generate the entire
-- query in a simpler format at runtime
--
select *
from (select 1 c1,2 c2,3 c3 from dual)
/

--
-- or, why not just use the even simpler version
-- 
select 1 c1,2 c2,3 c3 from dual
/

spool off