Re: Cursors within Cursors - is it possible?

[Quoted] Here is my code as it currently stands:

PROCEDURE multipleCursors

(outCursor OUT pjcCursor)

IS

    CURSOR itemCursor (rec_bndl_id NUMBER) IS

    SELECT         cat_itm_id

    FROM ITP_CATALOG_BUNDLE_ITEM     WHERE bndl_id = rec_bndl_id;

    CURSOR orderCursor IS

    SELECT         b.bndl_id,

        b.bndl_nm

    FROM ITP_CATALOG_BUNDLE b

    ORDER BY b.bndl_id;

BEGIN     DBMS_OUTPUT.PUT_LINE ('see me? ');

    FOR orderRecord in orderCursor

    LOOP         DBMS_OUTPUT.PUT_LINE ('bundle ' || orderRecord.bndl_id);

        FOR itemRecord in itemCursor (orderRecord.bndl_id)

        LOOP             DBMS_OUTPUT.PUT_LINE ('item ' || itemRecord.cat_itm_id);

        END LOOP;     END LOOP; END; It prints out a list of bundles (orders) and the items within. How can I combine these to come out of the procedure as 1 REF CURSOR?

Thanks!

"Patrick Casey" <patrick_casey_at_unc.edu> wrote in message news:95bp5q$ci$1_at_news2.isis.unc.edu...
> I have two tables (Order and OrderItems). I would like to create a
 procedure
> that returns back all Orders and for each Order, all of its items.
>
> Is it possible to create a procedure that returns a REF CURSOR that has a
> cursor within itself?
>
> So, it would be something like:
>
> ...
> TYPE testCursor is REF CURSOR;
> ...
> BEGIN
> OPEN testCursor FOR
> SELECT order_id, order_date
> FROM ORDER;
> END;
> ...
>
> How can I also get a cursor or items within that order for each order? I
> have tried many different guesses, but none will compile.
>
> Any help is greatly appreciated.
>
> --
>
> Thanks.
>
> Patrick Casey
>
>
>
Received on Thu Feb 01 2001 - 16:24:36 CET