My procedure compiles, however, I'm getting an error saying

exact fetch returns more than requested number of rows

I'm not sure how to fix this. This is the error I'm getting

01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

Here is my code.

create or replace type seat_list_type as varray(5) of varchar2(10);

create or replace procedure select_seat
    ( v_tid  int
    , v_seat seat_list_type )
as
    counter         number;
    v_seatselection int;
    v_quantity      int;
    v_status        int;
    v_available     int;
    v_count         int;
    v_sid           int;
begin
    -- Check whether transaction id is valid
    select count(tid) into counter from transaction where tid = v_tid;

    if counter < 1 then
        dbms_output.put_line('Transaction ID is not valid');
    else
        select seat_selection into v_seatselection
        from   transaction t
             , showtime    st
        where  t.show_id = st.show_id;

        if v_seatselection < 1 then
            dbms_output.put_line('This showtime has no seat selection');
        else
            select quantity into v_quantity
            from   transaction
            where  tid = v_tid;

            dbms_output.put_line('size is the same');

            if v_quantity <> v_seat.count then
                dbms_output.put_line('Size no match');
            else
                v_available := 1;

                for i in 1 .. v_seat.count loop
                    -- Check for v_seat(i), the ith selected seat, whether it is available,
                    -- this can be done by an implicit cursor with select count(*)
                    select count(*) into v_count
                    from   seat -- (the table storing status of seats)
                    where  s_status = 1;

                    if v_count = 0 then
                        dbms_output.put_line('seat ' || v_seat(i) || ' is not available');
                        -- if one seat is not available, change the flag.
                        v_available := 0;
                        exit;
                    end if;
                end loop;

                if v_available = 1 then
                    -- update each selected seat's status
                    update ticket
                    set    sid = v_seat
                    where  ticket_id =
                           ( select ticket_id
                             from   ticket_purchase
                             where  tid = v_tid
                             and    sid is null );
                end if;

            end if;
        end if;
    end if;
end;

0 Answers