Need to Handle IF condition in PL/SQL BULK Collect For Loop

Question

Team, I have source table

SRC_TBL 
--------------  
STATUS EMP_ID
--------------
VALID   E1
VALID   E2
VALID   E3
VALID   E4
VALID   E5
VALID   E6
INVALID E7
INVALID E8
VALID   E9
VALID   E10
VALID   E11
VALID   E12
VALID   E13
VALID   E14

1. Need to pick from SRC_TBL with STATUS ='VALID' and feed table TAR_TBL
2. Divide the total VALID records into batches with BATCH_ID with 5 recs/batch
3. This divide value 5 will be passed as parameter. Real case can be 100 or 200 per batch.
4. Sample data is just few here. Real case 10000+records in SRC_TBL table
5. Hence need to handle in BULK COLLECT
6. But use of IF/WHEN is not allowed in BULK COLLECT's forallin loop

TAR_TBL

STATUS  EMP_ID  BATCH_ID
VALID   E1      1
VALID   E2      1
VALID   E3      1
VALID   E4      1
VALID   E5      1
VALID   E6      2
VALID   E9      2
VALID   E10     2
VALID   E11     2
VALID   E12     2
VALID   E13     3
VALID   E14     3

Answer 1

Here's the code for your question.

DECLARE
  CURSOR c_src IS
    SELECT EMP_ID
    FROM SRC_TBL
    WHERE STATUS = 'VALID';

  TYPE emp_id_array IS TABLE OF SRC_TBL.EMP_ID%TYPE INDEX BY PLS_INTEGER;
  l_emp_ids emp_id_array;

  v_batch_size NUMBER := 5; -- You can change this to 100 or 200 based on your requirement.
  v_batch_id NUMBER := 1;

BEGIN
  OPEN c_src;

  LOOP
    FETCH c_src BULK COLLECT INTO l_emp_ids LIMIT v_batch_size;

    FORALL i IN 1..l_emp_ids.COUNT
      INSERT INTO TAR_TBL (STATUS, EMP_ID, BATCH_ID)
      VALUES ('VALID', l_emp_ids(i), v_batch_id);

    EXIT WHEN c_src%NOTFOUND;

    v_batch_id := v_batch_id + 1;
  END LOOP;

  CLOSE c_src;
END;
/

Answer 2

No need for PL/SQL code, simply do an INSERT with SELECT. Use row_number() window function to number the rows, and do a basic calculation to get 5 rows per batch.

insert into tar_tbl (STATUS, EMP_ID, BATCH_ID)
  select status, emp_id, trunc((rowno + 5 - 1) / 5) batch_id
  from (
    select status, emp_id, row_number() over (order by status) rowno
    from src_tbl 
    where status = 'VALID'
  ) dt

Replace the two (!) 5's with another number to adjust the batch size.

Demo: https://dbfiddle.uk/jdifUOdo