I am not able to use if statement every time it shows me error. Encountered symbol IF when expecting one of the following

Question

I am trying to add a conditional statement to run the INSERT statement. But it gives me error

'Encountered symbol IF when expecting one of the following: .(*%_-+/ at mod remainder rem select update with delete insert || execute multiset save merge.

I tried using case statement but that won't work

 declare

 TYPE tbl_id IS TABLE OF e0746851.temp_table.ID%type INDEX BY 
 BINARY_INTEGER;
 TYPE tbl_toc IS TABLE OF e0746851.temp_table.toc%type INDEX BY 
 BINARY_INTEGER;
 TYPE tbl_seq_fin_claim_link IS TABLE OF 
 nemis.fin_prof_header.seq_fin_claim_link_id%type INDEX BY 
BINARY_INTEGER;
TYPE tbl_sub_status IS TABLE OF e0746851.temp_table.sub_status%type 
INDEX BY 
BINARY_INTEGER;
TYPE tbl_resp_status IS TABLE OF e0746851.temp_table.rsp_status%type 
INDEX 
BY BINARY_INTEGER;
TYPE tbl_pp IS TABLE OF e0746851.temp_table.partial_status%type INDEX BY 
BINARY_INTEGER;
TYPE tbl_state IS TABLE OF e0746851.temp_table.state%type INDEX BY 
BINARY_INTEGER;

 v_toc            tbl_toc;
 v_fin_claim_link tbl_seq_fin_claim_link;
 c_count          number;
 v_sub            tbl_sub_status;
 v_resp           tbl_resp_status;
 v_id             tbl_id;
 v_pp_status      tbl_pp;
 v_state          tbl_state;


 var_state varchar2(10);
 var_toc varchar2(10);
 var_claim_id number;
 var_claim_version_id number;
 var_pps varchar2(10);
 var_flag varchar2(10); 
 var_claim_number varchar2(100);
 var_seq_fin_claim_link varchar2(10);
 var_resp varchar2(10);

 cursor CUR_SCENARIO is

   select tt.id,
       tt.state,
       tt.toc,
       tt.sub_status,
       tt.rsp_status,
       tt.partial_status
  from e0746851.temp_table tt;

 Begin

 open CUR_SCENARIO;
 loop
fetch CUR_SCENARIO bulk collect
  into v_id, v_state, v_toc, v_sub, v_resp, v_pp_status limit 10000;
exit when v_id.count() = 0;

delete from e0746851.Adjustment_result;

forall i in 1 .. v_id.count()

  if (substr(v_resp(i),1,3) <> 'MIX') then

  Insert into e0746851.Adjustment_result
         select 
         v_id(i),
         STATE,
         type_of_claim,
         CLAIM_ID,
         CLAIM_VERSION_ID,
         PARTIAL_PAID_DENIED,
         SUBMITTED_FLAG,
         CLAIM_NUMBER,
         SEQ_FIN_CLAIM_LINK_ID               
    from nemis.fin_prof_header
    where seq_fin_claim_link_id in  
         (select seq_fin_claim_link_id
          from nemis.fin_prof_header
           where state = v_state(i)
             and type_of_claim = v_toc(i)
             and claim_version_id > 2
             and seq_fin_claim_link_id in
                 (select f.seq_fin_claim_link_id
                    from nemis.state_sub_resp_hdr f
                   inner join (select max(insert_datetime) as max_date,
                                     seq_fin_claim_link_id
                                from nemis.state_sub_resp_hdr
                               where state = v_state(i)
                                 and type_of_claim = v_toc(i)
                                 and submission_status = v_sub(i)
                                 and response_status = v_resp(i)
                                 and insert_datetime > sysdate - 12
                               group by seq_fin_claim_link_id) h
                      on f.seq_fin_claim_link_id =
                         h.seq_fin_claim_link_id
                     and f.insert_datetime = h.max_date
                   where state = v_state(i)))
     and rownum < 10
   order by seq_fin_claim_link_id, claim_version_id;  

  end if;      


  end loop;
  close CUR_SCENARIO;

  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('UNEXPECTED ERROR OCCURED');
  ROLLBACK;
  end;

I expect that the code should run fine and data should be inserted successfully in the table

Answer 1

The documentation clearly indicates a FORALL must contain exactly 1 DML statement, and nothing else. It seems you have different requirements for when the IF is true and when false. I assume however both conditions can use the same bulk collection. If this is the case then you can move that condition into the where clause of the DML and then another FORALL for each additional condition. Your structure then becomes:

open cur_scenario;
loop
   fetch cur_scenario bulk collect
    into v_id, v_state, v_toc, v_sub, v_resp, v_pp_status limit 10000;
   exit when v_id.count() = 0;

   delete from e0746851.adjustment_result;

   forall i in 1 .. v_id.count
      insert into e0746851.adjustment_result
           select 
                v_id(i),
                ...         
            from nemis.fin_prof_header
           where substr(v_resp(i),1,3) <> 'mix'      --<<< your IF statement moved into where  when TRUE
             and seq_fin_claim_link_id in
                 ... ;

   forall i in 1 .. v_id.count
      insert into e0746851.adjustment_result
           select 
                v_id(i),
                ...         
            from nemis.fin_prof_header
           where substr(v_resp(i),1,3) = 'mix'      --<<< Alternate where IF was FALSE 
             and seq_fin_claim_link_id in 
                  ...; 

  end loop;
  close cur_scenario;

Answer 2

FORALL is not a loop, it is an integral part of the INSERT statement. So you can't use IF after FORALL.

Add the IF condition in your WHERE clause

....
and response_status = v_resp(i) AND substr(v_resp(i), 1, 3) <> 'MIX'
...