Home » RDBMS Server » Server Administration » Please help us ....
| Please help us .... [message #371911] |
Tue, 19 December 2000 11:56  |
asma
Messages: 12
Registered: December 2000
|
Junior Member |
|
|
** What's the wrong in this procedure it doesn't go through if clause it goes directly to else clause under any codition ...
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 VARCHAR2,
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_roomtype IN VARCHAR2)
is
begin
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,
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,conf#.nextval);
htp.p('First if');
elsif check_availability%rowcount < 30 and
p_roomtype ='excutive suite' then
insert into custlist
(name,phone,country,city,address,email,cridittype,criditno,
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,conf#.nextval);
htp.p('second if');
elsif check_availability%rowcount < 10 and p_roomtype =
'delux suite' then
insert into custlist
(name,phone,country,city,address,email,cridittype,criditno,
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,conf#.nextval);
htp.p('theard if');
else
htp.p('There is not available room from this type');
end if;
end;
end;
end;
Thanx..
|
|
|
|
| Re: Please help us .... [message #371914 is a reply to message #371911] |
Wed, 20 December 2000 09:45  |
laxmi
Messages: 9
Registered: December 2000
|
Junior Member |
|
|
Hi,
Are you sure, Your query is retreiving less than
100 records and at the same time the conditions in the 'AND' clause should be satisfied too.
One more suggestion:
When you are using '<' in the 'IF,ELSIF' statements the lowest number should be checked first.
ie: IF A < 2 then ....
Elsif A < 3 Then ... etc.
Otherwise the first 'IF' condition will always be
satisfied.
|
|
|
|
|
|
|
|
|
|
| Re: Please help us .... [message #371920 is a reply to message #371911] |
Wed, 20 December 2000 19:52 |
bc
Messages: 4
Registered: December 2000
|
Junior Member |
|
|
Can you try this from the sql prompt , put in the values for p_roomtype and p_arrivaldate ( put the values that you were passing as parameters to the procedure )
select count(*)
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);
What value do you get ?
|
|
|
|
| Re: Please help us .... [message #371925 is a reply to message #371920] |
Thu, 21 December 2000 14:07 |
Kavitha
Messages: 40
Registered: December 1999
|
Member |
|
|
I removed all the insert stmts & some begin-ends
from your proc.And I Put some messages.
Run it.Let me know what is the input value for p_roomtype.And what is the output you are getting.
Let us start from there.
And In the Fetch stmt,Check the order of variables(newconfirmation#, new2confirmation#).
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 VARCHAR2,
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_roomtype IN VARCHAR2)
is
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
dbms_output.put_line('First if stmt -'||check_availability%rowcount
||'..'||p_roomtype||'.');
elsif check_availability%rowcount < 30 and
p_roomtype ='excutive suite' then
dbms_output.put_line('Second if stmt -'||check_availability%rowcount
||'..'||p_roomtype||'.');
elsif check_availability%rowcount < 10 and
p_roomtype = 'delux suite' then
dbms_output.put_line('Third if stmt -'|| check_availability%rowcount
||'..'||p_roomtype||'.');
else
dbms_output.put_line( 'Else condn -'||check_availability%rowcount
||'..'||p_roomtype||'.');
end if;
exception
when others then
dbms_output.put_line( 'Exception-'||sqlerrm);
END;
|
|
|
|
|
|
| Re: Please help us .... [message #371948 is a reply to message #371920] |
Tue, 26 December 2000 10:52 |
asma
Messages: 12
Registered: December 2000
|
Junior Member |
|
|
I try your procedure with all p_roomtype (single, twin/double, excutive suite, delux suite ) but the output is nothing ...
* I work with oracle webDB did u think the wrong because of webDB even I run my old procudere and its go with if clause and insert the rows with all p_roomtype its the same procedure how goes directly to else clause with the same input..
* note : I sure its the same procedure and the same input I can't belive that and this problem is occur with the following procedure how goes with if clause for first time then when I go for next time to check it it goes to else clause without any reason can any one discribe this for my I can't belive that.
* Second Procedure
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 VARCHAR2,
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_roomtype IN VARCHAR2)
is
begin
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 rreservation.checkout > p_arrivaldate )
or ( 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 > 0 and 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, conf#.nextval);
htp.p('First if');
elsif (check_availability%rowcount > 0 and 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,conf#.nextval);
htp.p('second if');
elsif (check_availability%rowcount > 0 and 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,conf#.nextval);
htp.p('theard if');
else
htp.p('There is not available room from this type');
end if;
end;
end;
end;
Thanx;;
Asma
|
|
|
|
| Re: Please help us .... [message #371952 is a reply to message #371911] |
Tue, 26 December 2000 18:06 |
bc
Messages: 4
Registered: December 2000
|
Junior Member |
|
|
Can you try this from the sql prompt , put in the values for p_roomtype and p_arrivaldate ( put the values that you were passing as parameters to the procedure )
select count(*)
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);
What value do you get ?
|
|
|
|
Goto Forum:
Current Time: Fri May 17 01:05:18 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
