-
PL/SQL cursor not reading data from table
Hi,
I'm facing problems with my PL/SQL procedure; it appears to me that my cursor reads no value from the tables in my database.
My codes are as follows :
CREATE OR REPLACE PROCEDURE dayFlight(dept IN VARCHAR, arr IN VARCHAR, chdate IN VARCHAR) AS
fldate date;
fldate :=to_date(chdate, 'dd-mm-yyyy');
deptc integer;
arrc integer;
deptexc exception;
arrexc exception;
cnt integer:=0;
cursor dflto is
select f.fno, f.depcity, f.arrcity, f.depart_time, f.arr_time, l.fdate
from flights f, cities c1, cities c2, legs l
where c1.city=dept and c2.city=arr
and l.fdate=to_date(chdate,'DD-MM-YYYY')
and c1.citycode=f.depcity and c2.citycode=f.arrcity
and f.fno=l.flight;
begin
dbms_output.enable(10000);
select count(city) into deptc from cities where city=dept;
select count(city) into arrc from cities where city=arr;
if (deptc =0) AND (arrc=0) then
raise deparrexc;
elsif (arrc = 0) then
raise arrexc;
elsif (deptc =0 ) then
raise deptexc;
end if;
for df in dflto loop
if (dept=df.depcity) AND (arr=df.arrcity) AND (to_date(chdate,'DD-MM-YYYY')=df.fdate) then
if(cnt=0) then
dbms_output.put_line('The flights from ' || dept || ' to '|| arr || ' on ' || chdate || ' are:');
cnt:=cnt+1;
end if;
dbms_output.put_line('-------------------------------------------------');
dbms_output.put_line('Flight Number: ' || df.fno);
dbms_output.put_line('Departure Time: ' || df.depart_time);
dbms_output.put_line('Arrival Time: ' || df.arr_time);
dbms_output.put_line('-------------------------------------------------');
end if;
end loop;
if(cnt=0) then
dbms_output.put_line('There is no flight from ' || dept || ' to ' || arr || ' on ' || chdate ||'.');
end if;
exception
when deptexc then
dbms_output.put_line(dept ||' is an invalid city.');
when arrexc then
dbms_output.put_line(arr ||' is an invalid city.');
when deparrexc then
dbms_output.put_line('Both ' || dept ||' and ' arr ||' are invalid cities.');
end;
/
When I execute my procedure all I get are the exception messages. By default, my procedure will display that there are no flights between any specified two cities.
I feel that there might be something wrong in the way that I have declared my cursor, particularly with this condition :
<snip>
and l.fdate=to_date(chdate,'DD-MM-YYYY')
<snip>
I checked the manuals and books, apparently the way I've used to_date is correct.
I just can't understand where I am going wrong. I hope someone out there can help.
Thanks!
ashazi
-
RE
hi
can you post your exception messages??
further, what is the format in which chdate is stored in the database...
raajesh
-
Try this
Create Procedure with chdate as DATE datatype:
CREATE OR REPLACE PROCEDURE dayFlight(dept IN VARCHAR, arr IN VARCHAR, chdate IN DATE)
And In Cursor:
cursor dflto is
select f.fno, f.depcity, f.arrcity, f.depart_time, f.arr_time, l.fdate
from flights f, cities c1, cities c2, legs l
where c1.city=dept and c2.city=arr
and trunc(l.fdate)=chdate
and c1.citycode=f.depcity and c2.citycode=f.arrcity
and f.fno=l.flight;
----------------------------------------
I hope it does work!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|