PL/SQL cursor not reading data from table

[ashazi]

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

[Raajesh]

hi

can you post your exception messages??

further, what is the format in which chdate is stored in the database...

raajesh

[Maverick2000]

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!