Help with my del_flight procedure

My del_flight is returning an error can someone help me find out why?  here is my package followed by my tables


create or replace package AIRLINE_MOD as

procedure ADD_RES (aPass_ID IN VARCHAR2, aSSN IN CHAR, aflight_num IN CHAR);

procedure UP_RES (aPass_ID IN VARCHAR2, aflight_num in CHAR);

procedure DEL_RES (aPass_ID IN VARCHAR2);

procedure ADD_FLIGHT (aFlight_num IN CHAR, aairline IN VARCHAR2, adept_date IN DATE, aret_date IN DATE,
adept_time IN CHAR, aret_time IN CHAR, aseats IN CHAR, adestination IN CHAR);

procedure UP_FLIGHT (aFlight_num IN CHAR, aairline IN VARCHAR2, adept_date IN DATE, aret_date IN DATE,
adept_time IN CHAR, aret_time IN CHAR, aseats IN CHAR, adestination IN CHAR);

procedure DEL_FLIGHT (aFlight_num IN CHAR);

END AIRLINE_MOD;
/


create or replace package body AIRLINE_MOD as



procedure ADD_RES (aPass_ID IN VARCHAR2, aSSN IN CHAR, aflight_num IN CHAR)
is
begin
 insert into Reservation(Pass_ID, rSSN, rflight_num)
 VALUES(aPass_ID, aSSN, aflight_num);
 
 update Flights set seats = seats - 1
 where Flight_num = aflight_num;
end ADD_RES;



procedure UP_RES (aPass_ID IN VARCHAR2, aflight_num in CHAR)
is
original_flight CHAR(9);
begin
  select rflight_num  
  into original_flight from Reservation
  where Pass_ID = aPass_ID;

  update Flights set seats = seats + 1
  where original_flight = Flight_num;

  update RESERVATION set rflight_num = aflight_num
  where Pass_ID = aPass_ID;

  update Flights set seats = seats - 1
  where Flight_num = aflight_num;
end UP_RES;


procedure DEL_RES (aPass_ID IN VARCHAR2)
is
original_flight CHAR(9);
begin
  select rflight_num  
  into original_flight from Reservation
  where Pass_ID = aPass_ID;
 
  delete from Reservation
  where Pass_ID = aPass_ID;
 
  update Flights set seats = seats + 1
  where original_flight = Flight_num;
end DEL_RES;


procedure ADD_FLIGHT (aFlight_num IN CHAR, aairline IN VARCHAR2, adept_date IN DATE, aret_date IN DATE,
adept_time IN CHAR, aret_time IN CHAR, aseats IN CHAR, adestination IN CHAR)
is
begin
 insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
 VALUES(aFlight_num, aairline, adept_date, aret_date, adept_time, aret_time, aseats, adestination);
end ADD_FLIGHT;


procedure UP_FLIGHT (aFlight_num IN CHAR, aairline IN VARCHAR2, adept_date IN DATE, aret_date IN DATE,
adept_time IN CHAR, aret_time IN CHAR, aseats IN CHAR, adestination IN CHAR)
is
begin
 update Flights set airline = aairline, dept_date = adept_date, ret_date = aret_date, dept_time =

adept_time,
 ret_time = aret_time, seats = aseats, destination = adestination
        where Flight_num = aFlight_num;
end UP_FLIGHT;


procedure DEL_FLIGHT (aFlight_num IN CHAR)
is
begin
 delete from Flights
 where Flight_num = aFlight_num;
end DEL_FLIGHT;

     
end AIRLINE_MOD;
/



create table Pass_Info(SSN       CHAR(9)       PRIMARY KEY,
                       first_name    VARCHAR2(40)  NOT NULL,
                       last_name    VARCHAR2(40)  NOT NULL,
                       street    VARCHAR2(50)  NOT NULL,
                       city      VARCHAR2(50)  NOT NULL,
                       state     CHAR(2)       NOT NULL,
                       zip       CHAR(5)       NOT NULL,
                       phone     VARCHAR(12)   NOT NULL);


create table Flights(Flight_num       CHAR(5)        PRIMARY KEY,
                     airline          VARCHAR2(3)    
                   CHECK (airline IN ('TWA', 'SWA', 'AA', 'NWA', 'JB')),
                 dept_date        DATE           NOT NULL,
                     ret_date         DATE           NOT NULL,
                     dept_time        CHAR(4)        NOT NULL,
                     ret_time         CHAR(4)        NOT NULL,
                     seats            CHAR(180)      NOT NULL,
                     destination      CHAR(3)        NOT NULL
                       CHECK (destination IN ('OAK', 'LAX', 'PIT', 'DEN', 'DFW')));


create table Reservation(Pass_ID     VARCHAR2(5)   PRIMARY KEY,
                         rSSN        CHAR(9)      NOT NULL,
                           CONSTRAINT SSNFK FOREIGN KEY (rSSN)
                         REFERENCES Pass_Info(SSN),
                         rflight_num  CHAR(5)      NOT NULL,
                           CONSTRAINT FLIGHTFK FOREIGN KEY (rflight_num)
                        REFERENCES Flights(Flight_num));    

/*Populating Passenger Info */
insert into Pass_Info(SSN, first_name, last_name, street, city, state, zip, phone)
      VALUES('111111111', 'Uriah', 'Robins', '1908 cambridge', 'crofton', 'MD', '21114', '4107210404');

insert into Pass_Info(SSN, first_name, last_name, street, city, state, zip, phone)
      VALUES('222222222', 'David', 'Curry', '532 Meadow Mist', 'Odenton', 'MD', '21134', '4103050350');

insert into Pass_Info(SSN, first_name, last_name, street, city, state, zip, phone)
      VALUES('333333333', 'Chris', 'Williams', '123 fake st', 'East Bungalok', 'MI', '12323',

'7898786676');

insert into Pass_Info(SSN, first_name, last_name, street, city, state, zip, phone)
      VALUES('444444444', 'Jerome', 'Bettis', '1 winners lane', 'Pittsburgh', 'PA', '11111',

'4456568900');

insert into Pass_Info(SSN, first_name, last_name, street, city, state, zip, phone)
      VALUES('555555555', 'Hillary', 'Swank', '1435 Mockingbird', 'Hilltop', 'MD', '45456',

'3018431211');

insert into Pass_Info(SSN, first_name, last_name, street, city, state, zip, phone)
      VALUES('666666666', 'Jebediah', 'Springfield', '1313 evergreen lane', 'Springfield', 'KY',

'45454', '4766758787');



/*Populating Flight Info */
insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('111', 'AA', to_date('2/26/2007','MM/DD/YYYY'), to_date('3/4/2007','MM/DD/YYYY'), '1035',

'1523', '30', 'LAX');

insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('222', 'TWA', to_date('01/26/2007','MM/DD/YYYY'), to_date('1/30/2007','MM/DD/YYYY'),

'0000', '0930', '45', 'PIT');

insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('333', 'SWA', to_date('03/26/2007','MM/DD/YYYY'), to_date('3/30/2007','MM/DD/YYYY'),

'1435', '2100', '107', 'DEN');

insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('444', 'JB', to_date('04/26/2007','MM/DD/YYYY'), to_date('4/29/2007','MM/DD/YYYY'),

'1555', '0100', '110', 'DFW');

insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('555', 'NWA', to_date('02/18/2007','MM/DD/YYYY'), to_date('2/21/2007','MM/DD/YYYY'),

'0900', '1235', '70', 'OAK');

insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('666', 'AA', to_date('07/15/2007','MM/DD/YYYY'), to_date('7/22/2007','MM/DD/YYYY'),

'1230', '1600', '34', 'DEN');

insert into Flights(Flight_num, airline, dept_date, ret_date, dept_time, ret_time, seats, destination)
      VALUES('777', 'TWA', to_date('07/19/2007','MM/DD/YYYY'), to_date('7/21/2007','MM/DD/YYYY'),

'0845', '1700', '39', 'OAK');



/*Populating Reservation Info */
insert into Reservation(Pass_ID, rSSN, rflight_num)
      VALUES('pas1', '111111111', '111');

insert into Reservation(Pass_ID, rSSN, rflight_num)
      VALUES('pas2', '222222222', '222');

insert into Reservation(Pass_ID, rSSN, rflight_num)
      VALUES('pas3', '333333333', '333');

insert into Reservation(Pass_ID, rSSN, rflight_num)
      VALUES('pas4', '444444444', '444');

insert into Reservation(Pass_ID, rSSN, rflight_num)
      VALUES('pas5', '555555555', '555');

insert into Reservation(Pass_ID, rSSN, rflight_num)
      VALUES('pas6', '666666666', '666');    





Start Free Trial