Re: Oracle function returning set of rows

I was able to create the function
First you have to create a table object and then invoke a table type Here's how you do it

CREATE TYPE ABCD_REPORT AS OBJECT

       ( Field1 VARCHAR2(50),
        Field2   VARCHAR2(50),
        Field3      VARCHAR2(50),
       );

Then

CREATE TYPE ABCD_table AS TABLE OF ABCD_REPORT;

Here is the syntax

Create fuction FN_ABCD
(
reportdate in DATE
) RETURN ABCD_TBL PIPELINED is

 type ref0 is REF CURSOR;
 l_cursor ref0;

out_rec ABCD_REPORT := ABCD_REPORT(NULL,NULL,NULL); begin
open l_cursor for

  SELECT
  ....
  LOOP
    FETCH l_cursor INTO out_rec.Field1,
    out_rec.Field2,
out_rec.Field3 ;

    EXIT WHEN l_cursor%NOTFOUND;
    PIPE ROW(out_rec);
  END LOOP; close l_cursor;

return;

end FN_ABCD;

Here is how you invoke the function

select * from TABLE(FN_ABCD('3/5/2007') ); Received on Fri Aug 17 2007 - 08:56:54 CDT