Tuesday, 27 August 2013

PL/SQL Group By - ORA-01422: exact fetch returns more than requested number of rows

PL/SQL Group By - ORA-01422: exact fetch returns more than requested
number of rows

I am writing the following query that I want to display car registration,
car group name, model name, cost and the number of bookings for each car.
I have to use an explicit cursor and I have to use an implicit cursor to
calculate the number of bookings that belong to each car.
My query is as follows:
SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
Declare
v_count number;
cursor carcur IS
SELECT * FROM i_car;
v_car carcur%ROWTYPE;
Begin
Select COUNT (registration)
INTO v_count
from i_booking
group by registration;
FOR v_car IN carcur LOOP
DBMS_OUTPUT.PUT_LINE('Registration:'|| ' '|| v_car.registration);
DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
DBMS_OUTPUT.NEW_LINE;
END LOOP;
End;
The output I am getting is as follows: Declare * ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7
I am sure it has something to do with the return values being put into the
variable, but I have no idea how to rectify this.
Any advice would be greatly appreciated.
Many thanks.

No comments:

Post a Comment