Re: parameterized views

Galen,

I created the two types and then the function. I got a compilation error for the function:

Line # = 0 Column # = 0 Error Text = PL/SQL: Compilation unit analysis terminated
Line # = 2 Column # = 14 Error Text = PLS-00905: object RIS.C_TBL is invalid

I tried some variations for the type C_TBL, but all of them failed.

My server Oracle is 8.1.

Any idea ?

Best regards,
ernest

"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message news:u4qn97idd.fsf_at_standardandpoors.com...
> On Wed, 11 Aug 2004, ernest_at_gesora.com wrote:
> > Galen,
> >
> > Let's forget everything was discussed in this thread.
> > Let's say I present you my new problem:
>
>
> > 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; at least I wasn't able to find samples like this.
> >
> > 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 object/row type with pipelined ?
>
> Use a cursor.
>
> > If you want me be more specific, 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 */
>
> Okay,
>
> Try this untested code:
>
> TYPE r_tbl IS RECORD (
> a_id MyTable.id%TYPE,
> a_firstName MyTable.FirstName%TYPE,
> a_lastName MyTable.LastName%TYPE
> );
>
> TYPE c_tbl IS REF CURSOR
> RETURN r_tbl;
>
> FUNCTION GetMyData (prm IN varchar2)
> RETURN c_tbl
> AS
> v_tbl c_tbl;
> BEGIN
> OPEN v_tbl FOR
> SELECT ID a_id
> ,FirstName a_firstName
> ,LastName a_lastName
> FROM MyTable
> WHERE FirstName like prm
> ;
> RETURN v_tbl;
> END GetMyData;
>
>
> --
> Galen Boyer
Received on Thu Aug 12 2004 - 04:42:03 CDT