Re: function that returns a cursor

Ernest Morariu wrote:

> Hi All !
>
> I want to create a function/procedure that takes some parameters, make some
> processing and finally return a resultset/cursor/table to the client(ADO).
>
> My problem is that I don't know how to return the resultset. In the
> documentation, all of the samples of procedures/functions show how to
> insert/delete/update records but they do not show how to return resultsets.
>
> I don't know what to declare as return type for a function returning a
> resultset. Should I use a TableType ? a cursor ? a generic cursor ? a
> record type ? a object row type with pipelined ?
>
> Consider I want to return a resultset having the following structure :
> ID Number(16,0)
> FirstName varchar2(30)
> LastName varchar2(30)
>
> Help me to create a function like this:
>
> Create function GetMyData(prm varchar2) return <??????>
> AS
> BEGIN
> -- doing some things
>
> /*
> show me how to return the result of the following command to the client :
> SELECT ID, FirstName, LastName From MyTable Where FirstName Like prm
> */
>
> END;
>
> Best regards,
> ernest

Return the result set with a REF CURSOR. http://www.psoug.org/reference/ref_cursors.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)