Need to accept comma separated inputs from the stored procedure and have to process as I have explained in the below body but getting compilation err

Question

CREATE TABLE STAGING
(
    E_ID NUMBER(10),
    E_NAME VARCHAR2(30),
    E_LOC VARCHAR2(30),
    VALIDATION_STATUS varchar2(30),
    validation_msg varchar2(30), 
    req_id number(10) 
);

INSERT INTO staging VALUES(1, 'A', 'AA', NULL, NULL, 1);
INSERT INTO staging VALUES(2, 'B', 'BB', NULL, NULL, 1);
INSERT INTO staging VALUES(3, 'C', 'CC', NULL, NULL, 1);
INSERT INTO staging VALUES(NULL, 'D', 'DD', NULL, NULL, 2);
INSERT INTO staging VALUES(NULL, 'E', 'EE', NULL, NULL, 2);
INSERT INTO staging VALUES(NULL, 'F', 'GG', NULL, NULL, 2);

CREATE TABLE tab_ref
(
    ref_id number(10),
    ref_name varchar2(30)
);

INSERT INTO tab_ref VALUES(1, 'aa');
INSERT INTO tab_ref VALUES(2, 'bb');
INSERT INTO tab_ref VALUES(3, 'cc');
INSERT INTO tab_ref VALUES(4, 'dd');

CREATE TABLE tab_ref_2
(
    ref_id number(10),
    ref_name varchar2(30)
);

INSERT INTO tab_ref_2 VALUES(1, 'ee');
INSERT INTO tab_ref_2 VALUES(2, 'ff');
INSERT INTO tab_ref_2 VALUES(3, 'gg');
INSERT INTO tab_ref_2 VALUES(4, 'hh');

CREATE TABLE SUMMARY_TAB
(   
    TOT_RECORDS NUMBER(10,0), 
    SUCCESS_RECORDS NUMBER(10,0), 
    FAILED_RECORDS NUMBER(10,0),
    process_status varchar2(30)
);

CREATE TABLE TARGET_TAB
(   
    E_ID NUMBER(10,0), 
    E_NAME VARCHAR2(30), 
    E_LOC VARCHAR2(30)
);

Stored procedure:

    create or replace procedure sp_stage_target(iv_req_id IN sys.OdciNumberList,ov_err_msg OUT varchar2) is
  lv_succ_rec number(30);
  lv_fail_rec number(30);
  lv_count_ref number(10);
  lv_count_ref2 number(10);
  lv_threshold_cnt number(10);
  lv_RejectedCount number(10);
  lv_status varchar2(30);
begin
  lv_succ_rec := 0;
  lv_fail_rec := 0;
  lv_threshold_cnt := 5;
    
    /*First checking whether data is present in reference table or not. 
    If data is not present then process should stop*/
  select count(1) into lv_count_ref from tab_ref;
    
  select count(1) into lv_count_ref2 from tab_ref_2;
    
  if lv_count_ref = 0 then
    ov_err_msg := 'Records are not present in the reference table !!Cannot proceed';  
  elsif lv_count_ref2 = 0 then
    ov_err_msg := 'Records are not present in the reference table !!Cannot proceed';      
  else
  
  dbms_output.put_line('Data are present into reference tables');
  
    merge into staging d
    using (
      select 'Fail' as validation_status, t.column_value as req_id
      from   table(iv_req_id) t
    ) s
    on (d.req_id = s.req_id)
    when matched then
      update set
        d.validation_status = s.validation_status
      , d.validation_msg = case
                             when e_id is null then 'Id is not present'
                             else 'Id is longer than expected'
                           end
      where e_id is null OR LENGTH(e_id) > 4;
    lv_RejectedCount := SQL%ROWCOUNT;
  end if;


--If rejected count is less than lv_threshold_cnt i.e 5 
--then success records will go in target_tab and failed records will go in reject_tab


  if lv_RejectedCount <= lv_threshold_cnt  then
    lv_status := 'Success';

dbms_output.put_line('Success');

    merge into target_tab t
    using (
      select e_id, e_name, e_loc
      from   staging
      where  validation_status is null and req_id in (select column_value from table(iv_req_id))
    ) s
    on (t.e_id = s.e_id)
    when matched then 
      update set 
        t.e_name = s.e_name,
        t.e_loc = s.e_loc
    when not matched then 
      insert (t.e_id,t.e_name,t.e_loc)
      values (s.e_id,s.e_name,s.e_loc);
    lv_succ_rec := SQL%ROWCOUNT;
  end if;


  insert into reject_tab
  select e_id, e_name, e_loc, validation_status,validation_msg
  from   staging 
  where  validation_status = 'Fail' and req_id in (select column_value from table(iv_req_id));


  lv_fail_rec := SQL%ROWCOUNT;


--In Summary table keeping track of all the records i.e success record, failed records
  dbms_output.put_line('Inserting into Summary table');
  insert into summary_tab(tot_records, success_records, failed_records, process_status)
  values (lv_succ_rec + lv_fail_rec, lv_succ_rec, lv_fail_rec, lv_status);
  ov_err_msg := 'Procedure completed succesfully';
end;

Calling Procedure:

set serveroutput on;
declare
err_msg;
begin
sp_main_target(sys.OdciNumberList(1,2),err_msg);
dbms_output.put_line(err_msg);
end;

Getting compilation error and also I am not not how to process for individually for each request_id and process so have highlighted the requirement in comment block.

Error :

1. I wanted to create a stored procedure that will handle all the below points and I have tried but not been able to get the results.
2. The stored procedure should accept multiple input parameters while calling a procedure and should process for every request-id given in the parameter with comma-separated.
3. Then stored procedure will check whether data is present or not in the ref table (tab_ref & tab_ref_2). If data is present then only the process should start otherwise it will not proceed further.
4. Then it will check the data in the staging table and will do validation for e_id is null or not or its length should not exceed the given limit. For every failed validation it will update the validation status and validation msg column and have to keep count of rejected columns.
5. After validation, if the threshold count is less then the lv_threshold_count then insertion will happen in both the tables with status as 'Success 'i.e target table and rejected table with validation_status as null and Fail respectively.
6. If threshold count is more than the lv_threshold_count then it will insert into the rejected table with status as 'Fail'.
7. Then at last it will show all the records count into the summary table.

Answer 1

You start an IF on line 20 of your procedure, but you don't have a corresponding END IF.

if lv_count_ref = 0 then