SQL & PL/SQL

I am new to collections and think I am making things more complicated….would love your thoughts.

I have created two collections, t_raw_fl and t_failed_raw. As I run through each record of T_RAW_FL I want to check the data for valid date, valid number, etc. If the row fails validation, I want to write it to the 2nd collection T_FAILED_RAW. At the end of processing, I would like to use FORALL to insert the records from T_FAILED_RAW into the production table DATA_LOG_ERRORS.

the statement in VALIDATE_DATE to insert record into l_fail is failing with pls-00316.

thanks for any help

my package specification defines each collection:

  TYPE t_raw_fl IS TABLE OF raw_data_florida%ROWTYPE 
 index by binary_integer;
 TYPE t_failed_raw IS TABLE OF atsea_data_logs%ROWTYPE
   INDEX BY BINARY_INTEGER;

and the body contains the following procedures:

procedure test_bulk  (p_event_id in number)
is
CURSOR raw_fl  IS  SELECT *
                   FROM raw_data_florida
                   WHERE event_id = p_event_id;   
   cursor fail_fl is select * from atsea_data_logs where event_id = p_event_id;
   
   v_app_id atsea_data_logs.app_id%TYPE;
   
BEGIN
   v_app_id := 'ASO';
   
   atsea_util.clear_errors_event( p_event_id); 
   
   OPEN raw_fl;
   FETCH raw_fl BULK COLLECT INTO l_raw_fl;
     
   – NOT CERTAIN IF I NEED TO INITIALIZE THIS COLLECTION WHICH SHOULD BE EMPTY AT START
   open fail_fl;
   fetch fail_fl bulk collect into l_fail;
   
   for i in (SELECT *  FROM TABLE(l_raw_fl))
   loop
       validate_date(v_app_id,p_event_id, i.upload_seq, i.startdate,'YYYY/MM/DD','StartDate');
       validate_date(v_app_id,p_event_id, i.upload_seq, i.enddate,'YYYY/MM/DD','EndDate');
    
   end loop;
end test_bulk;

within the VALIDATE_DATpackage

procedure validate_date   (         p_app_id       in varchar2,
                                   p_event_id     in number, 
                                   P_upload_seq   in number,
                                   p_date    in varchar2 ,
                                   p_format in varchar2,
                                   p_column in varchar2)
is
begin
--errors:
-- 40 - value may not be null
-- 20 - invalid date
-- 21 - date may not be in the future
 
       if ATSEA_UTIL.is_date_format(p_date,p_format) = 'FALSE' then
  
           -- populate collection l_fail
          l_fail(1) := l_fail(1,p_event_id,p_upload_seq,'E',20,'error test',p_app_id,p_column,p_date);
     
       end if;
   end if;
exception when others then
  atsea_logger.log_data_message( p_event_id, p_upload_seq,  20,'E',p_app_id,  p_date, p_column);
end validate_date ;