Re: Need Help with Stored Procedure

In article <35ki4u$6q7_at_bcarh8ab.bnr.ca>, sticklan_at_bnr.ca (Karla Dods) writes:
I'm trying to run a multi-row select procedure within SQLPLUS and I am receiving the error message ORA-1422 exact fetch returns more than requested rows. Could someone please point me to an example which I can learn how to code a multi-row select procedure Your responses will be greatly appreciated.

My response:

declare

    cursor L_ORDER is

        select ORDER_ID, FAC_ID, ORDER_DATE
        from ORDER 
        where . . . 

begin

.
.
.

    for ORDERS in L_ORDER loop

        .
        .
        .

     end loop;

The above is a cursor and a cursor for loop. The combination gives you a straightforward way to access all items in all the table that match your "where" clause of the select statement. The individual items such as ORDER_ID are access like:

        L_ORDER_ID := ORDERS.ORDER_ID; note that ORDERS is declared only in the for loop and is only available in the for loop. Also note that no exception handling is required for NO_DATA_FOUND. The loop will terminate after the last item. I hope this helps Received on Tue Sep 20 1994 - 17:25:02 CEST