hi friend ,
i am currently working in health care.
i have made procedure . its code is given below
create or replace
procedure bill_summary
(
PT_ID number , -------------------------------------> this is patient id
PT_ADMIT_DATE date, -------------------------------.> patient admit date
PT_DISCHARGE_DATE date -----------------------------> patient discharge date
)
as
days number(10);
room number(10);
nursing number(10);
doctor number(10) ;
lab number(10) ;
med number(10) ;
adv number(10) ;
con number(10) ;
ser number(10) ;
begin
select sum(no_of_days),sum (total_room_charges),sum (nursing_charges) ,sum (doctor_charges)
into days,room,nursing,doctor from ipd_dtls
where p_id = PT_ID and
examin_date between 'PT_ADMIT_DATE' and 'PT_DISCHARGE_DATE' ;
select sum (nvl(lab_charges,0)) into lab from lab_test_dtls
where p_id = PT_ID and
test_date between 'PT_ADMIT_DATE' and 'PT_DISCHARGE_DATE';
select sum (medicine_charges) into med from drug_dtls
where p_id = PT_ID and
drug_date between 'PT_ADMIT_DATE' and 'PT_DISCHARGE_DATE';
select advance into adv from patient_treatment_dtls
where p_id = PT_ID and
examin_date = 'PT_ADMIT_DATE' ;
select sum ( nvl(get_service_charges,0)) into ser from patient_get_service
where p_id = pt_id and
from_get_service_date between 'PT_ADMIT_DATE' and 'PT_DISCHARGE_DATE' ;
select
(case hs_id
when 5000 then 1
when 5001 then .80
when 5002 then .85
when 5003 then .95
end ) into con from patient_registration
where p_id = PT_ID ;
insert into bill (bill_id,bill_date,p_id,doctors_charges,medicine_charges,nursing_charges,room_charges,
lab_charges,no_of_days,advance,consession,pay_bill,admit_date,discharge_date,service_charges)
values (bill_seq.nextval,sysdate,pt_id,doctor,med,nursing,room,lab,days,adv,
((doctor+ med + nursing + room + lab + ser )-adv)* con,
((doctor+ med + nursing + room + lab + ser )-adv)-((doctor+ med + nursing + room + lab + ser )-adv)* con
,pt_admit_date,pt_discharge_date,ser);
end bill_summary ;
exec bill_summary (3001,to_date ('07-JUL-12','dd-mon-yy'),to_date ('08-JUL-12','dd-mon-yy')) ;
my question is when i execute the procedure & i am passing
number & date as a input parameter in my procedure then i always get the error
ORA-01858: a non-numeric character was found where a numeric was expected
Error starting at line 1 in command:
exec bill_summary (3001,to_date ('07-JUL-12','dd-mon-yy'),to_date ('08-JUL-12','dd-mon-yy'))
Error report:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "HOSPITAL.BILL_SUMMARY", line 19
ORA-06512: at line 1
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
i am frustrated with this error.......... but i did not get the solution .........please can anyone tell the solution of
this error.
regads
zam