SQL & PL/SQL

Hello Guru,

I have a procedure and i want to find the count of CUR_PALLIST in procedure body part. Because if this ref cursor does not return any record, it should go to the exception block for NO_DATA_FOUND which i have mentioned. But here it is not going to the exception block. So please help me on this...

create or replace
PROCEDURE SP_GENERATE_PAL (I_DEPARTURE_STATION_CODE IN VARCHAR2,
I_DEPARTURE_DATETIME IN VARCHAR2,
I_OPR_FLIGHT_NUMBER IN VARCHAR2,
I_OPR_SERVICE_PROVIDER_CODE in varchar2,
I_OPR_FLIGHT_SUFFIX IN VARCHAR2 DEFAULT NULL,
CUR_PALLIST OUT SYS_REFCURSOR,
o_error OUT VARCHAR2)

IS

V_count NUMBER;

begin

OPEN CUR_PALLIST FOR
SELECT FL.DEPARTURE_STATION_CODE
,TO_CHAR(FL.DEPARTURE_DATETIME, 'DD/MM/YYYY HH24:MI:SS') DEPARTURE_DATETIME
,FL.OPR_FLIGHT_NUMBER
,FL.OPR_FLIGHT_SUFFIX
,FL.OPR_SERVICE_PROVIDER_CODE
,CT.ROMANISED_SURNAME
,CT.ROMANISED_GIVEN_NAME
,CT.ROMANISED_TITLE
,CT.ROMANISED_NAME_INITIALS
,CT.ROMANISED_NAME_SUFFIX
,CT.ROMANISED_MIDDLE_NAME
,CA.ADDTNL_INFO_TEXT
,CA.CUST_ASSISTANCE_TYPE_CODE
,CA.CUST_ASSIST_EQUIPMENT_CODE
FROM FLIGHT_LEG FL
,CUST CT
,CUST_FLIGHT_LEG CFL
,CUST_CUST_ASSISTANCE CCA
,CUST_ASSISTANCE CA

WHERE FL.DEPARTURE_STATION_CODE = I_DEPARTURE_STATION_CODE
AND FL.DEPARTURE_DATETIME = TO_DATE(I_DEPARTURE_DATETIME, 'DD-MON-YYYY HH24.MI.SS')
AND FL.OPR_FLIGHT_NUMBER = I_OPR_FLIGHT_NUMBER
and FL.OPR_SERVICE_PROVIDER_CODE= I_OPR_SERVICE_PROVIDER_CODE
and FL.OPR_FLIGHT_SUFFIX = NVL(I_OPR_FLIGHT_SUFFIX, FL.OPR_FLIGHT_SUFFIX)
AND FL.FLIGHT_LEG_ID = CFL.FLIGHT_LEG_ID
AND CFL.CUST_ID = CT.CUST_ID
AND CT.CUST_ID = CCA.CUST_ID(+)
AND CCA.CUST_ASSISTANCE_ID = CA.CUST_ASSISTANCE_ID(+)
and FL.HISTORY_VERSION_NUMBER = 0
and FL.EMERGENCY_LOCK_INDICATOR 'Y'
AND CFL.HISTORY_VERSION_NUMBER = 0
AND CT.HISTORY_VERSION_NUMBER = 0
AND NVL(CA.HISTORY_VERSION_NUMBER,0) = 0
AND NVL(CCA.HISTORY_VERSION_NUMBER,0) = 0;

EXCEPTION
when NO_DATA_FOUND THEN

select COUNT(*) into V_count from FLIGHT_LEG FL
where FL.DEPARTURE_STATION_CODE = I_DEPARTURE_STATION_CODE
and FL.DEPARTURE_DATETIME = TO_DATE(I_DEPARTURE_DATETIME, 'DD-MON-YYYY HH24.MI.SS')
and FL.OPR_FLIGHT_NUMBER = I_OPR_FLIGHT_NUMBER
and FL.OPR_SERVICE_PROVIDER_CODE= I_OPR_SERVICE_PROVIDER_CODE
and FL.OPR_FLIGHT_SUFFIX = NVL(I_OPR_FLIGHT_SUFFIX, FL.OPR_FLIGHT_SUFFIX)
and FL.HISTORY_VERSION_NUMBER = 0;

if V_count > 0 then
o_error := '-20001 - Unable to Generate Passenger Assitance List due to Emergency Lock';
else
o_error := '-20002 - Unable to Generate Passenger Assitance List due to Invalid Flight Summary';
end if;
return;
end SP_GENERATE_PAL;

Thanks,
HP..