| ORA-01422: exact fetch returns more than requested number of rows [message #20294] |
Sat, 11 May 2002 16:01  |
kam
Messages: 3
Registered: May 2002
|
Junior Member |
|
|
i have a stored fuction as:
set serveroutput on;
create or replace function totalbill
(i_patientno patient.patientno%type)
return number
is
v_total_bill patient.bill%type;
begin
select ((costperday*noofdays)+cost)
into v_total_bill from
(SELECT T.TREATMENTNO, T.COST, CS.PATIENTNO, R.COSTPERDAY, P.NOOFDAYS
FROM TREATMENT T, CONSULTATION CS, ROOM R, PATIENT P
WHERE T.TREATMENTNO=CS.TREATMENTNO
AND R.ROOMNUMBER=P.ROOMNUMBER AND CS.PATIENTNO=P.PATIENTNO);
if v_total_bill is null then
v_total_bill :=0;
end if;
-- to zero
return v_total_bill;
end;
/
and a stored procedure AS
set serveroutput on;
create or replace procedure calculatebill
as
cursor c_patient
is
select patientno
from patient;
v_total_bill patient.bill%type;
v_patientno patient.patientno%type;
begin
open c_patient;
loop
fetch c_patient into v_patientno;
exit when c_patient%notfound;
v_total_bill := totalbill(v_total_bill);
update patient
set bill = v_total_bill
where patientno=v_patientno;
end loop;
close c_patient;
end;
/
and when i execute the procedure i get the following error:
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.TOTALBILL", line 9
ORA-06512: at "SCOTT.CALCULATEBILL", line 18
ORA-06512: at line 1
anybody that can help it will be gratefull
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
