Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table SRC_TBL (status varchar2(10), emp_id varchar2(5));
insert into SRC_TBL
select 'VALID',   'E1' from dual union all
select 'VALID',   'E2' from dual union all
select 'VALID',   'E3' from dual union all
select 'VALID',   'E4' from dual union all
select 'VALID',   'E5' from dual union all
select 'VALID',   'E6' from dual union all
select 'INVALID', 'E7' from dual union all
select 'INVALID', 'E8' from dual union all
select 'VALID',   'E9' from dual union all
select 'VALID',   'E10' from dual union all
select 'VALID',   'E11' from dual union all
select 'VALID',   'E12' from dual union all
select 'VALID',   'E13' from dual union all
select 'VALID',   'E14' from dual;
14 rows affected

create table TAR_TBL (status varchar2(10), emp_id varchar2(5), batch_id integer);
insert into TAR_TBL
select status, emp_id, trunc((rowno + 4) / 5) batch_id
from (
  select status, emp_id, row_number() over (order by status) rowno
  from src_tbl 
  where status = 'VALID'
) dt
12 rows affected

select * from 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

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