| ORA-01422, ORA-06512 [message #20296] |
Sun, 12 May 2002 05:10  |
kam
Messages: 3
Registered: May 2002
|
Junior Member |
|
|
i have function as following:
create or replace function totalbill
(i_patientno patient.patientno%type)
return number
is
v_total_bill patient.bill%type;
v_patientno patient.patientno%type;
begin
select ((R.costperday * P.noofdays)+ T.cost), P.PATIENTNO
into v_total_bill, v_patientno
from
TREATMENT T, ROOM R, PATIENT P, CONSULTATION CS
WHERE R.ROOMNUMBER=P.ROOMNUMBER AND CS.TREATMENTNO=T.TREATMENTNO AND P.PATIENTNO=CS.PATIENTNO;
if v_total_bill is null then
v_total_bill :=0;
end if;
return v_total_bill;
end;
/
and 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;
/
i keep getting ORA-01422: exact fetch returns more than requested number of ro
ORA-06512: at "U0114895.TOTALBILL", line 10
ORA-06512: at "U0114895.CALCULATEBILL", line 18
ORA-06512: at line 1
|
|
|
|
| Re: ORA-01422, ORA-06512 [message #20297 is a reply to message #20296] |
Sun, 12 May 2002 06:48  |
vivek
Messages: 59
Registered: October 2001
|
Member |
|
|
Your query below -
select ((R.costperday * P.noofdays)+ T.cost), P.PATIENTNO
into v_total_bill, v_patientno
from
TREATMENT T, ROOM R, PATIENT P, CONSULTATION CS
WHERE R.ROOMNUMBER=P.ROOMNUMBER AND CS.TREATMENTNO=T.TREATMENTNO AND P.PATIENTNO=CS.PATIENTNO;
returns more than one row which means that the where condition is retrieving more than one row so it is trying to put all these values into the variables at the same time. u will have to modify the query so that it returns only one row. Run it in SQL*PLUS and see how many rows it returns . It will return more than one. SO u will knwo where the problem lies. If u send me u're table structure and what u want i can probably help u out with the procedure.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
