Re: Stored Procdure Returning Ref Cursor

From: <rtproffitt_at_my-deja.com>
Date: Tue, 28 Sep 1999 16:34:43 GMT
Message-ID: <7sqqn2$d9r$1_at_nnrp1.deja.com>


You may need to declare the RETURN in the reference cursor, like so:

> package types
> as
> type CursorType is ref cursor;
> end;

package types as
  Type CursorType is REF CURSOR RETURN tbl%ROWTYPE end;

In your case, you need a little more complicated structure. How about either:
1) create a view, then declare ...Return ViewName%ROWTYPE or
2) Create a record definition and RETURN the record definition...

Type MyRec IS RECORD
  (po_A po_docs.po_A%type,
   po_B po_docs.po_B%type,
   ....
   po_it_A po_items.po_it_A%type,
   po_it_B po_Items.po_it_B%type
   ....);

.... RETURN MyRec;

You probably should not use select * in your query but rather list the elements you want...
>
> PROCEDURE BRIAN_PO_DOCS_CURSOR (C2 IN OUT types.CursorType) as
> BEGIN
> OPEN C2 for SELECT *
> FROM PO_DOCS , PO_ITEMS WHERE PO_DOCS .PO_DOC_ID =
> PO_ITEMS .PO_DOC_PO_DOC_ID;
> END;
>
....OPEN C2 for SELECT PO_A, PO_B, PO_IT_A... etc.....

Good Luck,
Robert Proffitt
Beckman Coulter
Brea California
RTProffitt AT beckman DOT com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 28 1999 - 18:34:43 CEST

Original text of this message