We have a project about hotel reservation .. we wrote procedure to reserve a room for new customer .When new customer reserve room by fill form we insert it after check for availabilty in wait list .. we don't insert it in hotel database untill he arrive ...
****we wrote the following procedure that give us execution error
create or replace
procedure findroom(
p_name IN VARCHAR2,
p_phone IN NUMBER,
p_country IN VARCHAR2,
p_city IN VARCHAR2,
p_address IN VARCHAR2,
p_email IN VARCHAR2,
p_cridittype IN VARCHAR2,
p_criditno IN NUMBER,
p_criditexp IN DATE,
p_nameoncard IN VARCHAR2,
p_arrivaldate IN DATE,
p_arrivaltime IN VARCHAR2,
p_am_pm IN VARCHAR2,
p_checkout IN DATE,
p_numadult IN NUMBER,
p_numchildren IN NUMBER,
p_confirmation# IN NUMBER,
p_roomtype IN VARCHAR2)
is
begin
declare
newconfirmation# custlist.confirmation#%type;
new2confirmation# rreservation.custconfirmation#%type;
cursor check_availability is
select custconfirmation#,confirmation#
from room , rreservation , custlist
where (Room.roomno = rreservation.custroomno and Room.roomtype = p_roomtype and rreservation.checkout > p_arrivaldate )
or (custlist.roomtype = p_roomtype and custlist.checkout > p_arrivaldate);
begin
open check_availability;
loop
fetch check_availability into newconfirmation#, new2confirmation#;
exit when check_availability%NOTFOUND;
end loop;
if check_availability%rowcount < 100 and (p_roomtype ='single' or p_roomtype ='twin/doubl')
then
insert into custlist (name,phone,country,city,address,email,cridittype,criditno,
criditexp,nameoncard,arrivaldate,arrivaltime,am_pm,checkout,numadult,numchildren,roomtype,confirmation#)
values (p_name,p_phone,p_country,p_city,p_address,p_email,
p_cridittype,p_criditno,p_criditexp,p_nameoncard,
p_arrivaldate,p_arrivaltime,p_am_pm,p_checkout,p_numadult,
p_numchildren,p_roomtype,'1111110');
end if;
if check_availability%rowcount < 30 and p_roomtype ='excutive suite'
then
insert into custlist (name,phone,country,city,address,email,cridittype,criditno,
criditexp,nameoncard,arrivaldate,arrivaltime,am_pm,checkout,numadult,numchildren,roomtype,confirmation#)
values (p_name,p_phone,p_country,p_city,p_address,p_email,
p_cridittype,p_criditno,p_criditexp,p_nameoncard,
p_arrivaldate,p_arrivaltime,p_am_pm,p_checkout,p_numadult,
p_numchildren,p_roomtype,'1111110');
end if;
if check_availability%rowcount < 10 and p_roomtype =
'delux suite'
then
insert into custlist (name,phone,country,city,address,email,cridittype,criditno,
criditexp,nameoncard,arrivaldate,arrivaltime,am_pm,checkout,numadult,numchildren,roomtype,confirmation#)
values (p_name,p_phone,p_country,p_city,p_address,p_email,
p_cridittype,p_criditno,p_criditexp,p_nameoncard,
p_arrivaldate,p_arrivaltime,p_am_pm,p_checkout,p_numadult,
p_numchildren,p_roomtype,'1111110');
end if;
end;
end;/
**** the error ***
Error Executing Cursor
SQL:
begin
"SCOTT"."FINDROOM" ( P_NAME => 'miral', P_ARRIVALDATE => '11-dec-00', P_CHECKOUT => '2-jan-01', P_CONFIRMATION# => 1111119, P_ROOMTYPE => 'single');
end;
ORA-01001: invalid cursor
*** please help us to know what is the error in this code we haven't enough time.
Thanx....
|
] 
Blog
Search
Help
Members
Register
Login
Home
