 -- Function used in get_seat.pc (CA2053/2081) to obtain passenger seat number from the database

 --                parameters: p_flight - the passenger's flight_id
 --                            p_security - the passenger's security number
 --                   returns: the passenger's seat number (varchar2)          

create or replace function get_seat_no(p_flight IN FLIGHT.FLIGHT_ID%TYPE,
                                       p_security IN PAX.PAX_SECURITY_NO%TYPE) return varchar2 AS 
   l_seatno varchar2(4);

begin
   SELECT pax.pax_seat_no INTO l_seatno 
     FROM pax
    WHERE pax.pax_security_no = p_security                --  select passenger seat number into          
      AND pax.flight_id = p_flight;                       --  l_seatno variable
   
   return l_seatno;

exception

   when NO_DATA_FOUND then                   -- ensure that if SQL returns error, we inform                       
       return 'ERR';                         -- the calling Pro*C function
   when TOO_MANY_ROWS then
       return 'ERR';

end get_seat_no;
/
