Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
create table tableA
(
    Job_id varchar2(50),
    Job_type varchar2(50),
    Employee_list clob,
    current_date date,
    salary varchar2(15)
);
create table tableB
(
    Job_id varchar2(50),
    Employee varchar2(20),
    current_date date,
    salary varchar2(15)
); 
INSERT INTO tableA (job_id, job_type, employee_list, current_date, salary)
SELECT  10102, 'Non Managerial', 'Steven, Bob', DATE '1999-01-01', 2e4 FROM DUAL UNION ALL
SELECT 102033, 'Managerial', 'David, Charlie, Keren', DATE '1999-01-02', 3e4 FROM DUAL;
2 rows affected

COMMIT;
INSERT INTO tableb (job_id, employee, current_date, salary)
WITH bounds (job_id, employee_list, current_date, salary, spos, epos) AS (
  SELECT job_id,
         employee_list,
         a.current_date,
         salary,
         1,
         INSTR(employee_list, ',', 1)
  FROM   tablea a
UNION ALL
  SELECT job_id,
         employee_list,
         b.current_date,
         salary,
         epos + 1,
         INSTR(employee_list, ',', epos + 1)
  FROM   bounds b
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY job_id SET order_id
SELECT job_id,
       TRIM(
         CASE epos
         WHEN 0
         THEN SUBSTR(employee_list, spos)
         ELSE SUBSTR(employee_list, spos, epos - spos)
         END
       ),
       b.current_date,
       salary
FROM   bounds b;
5 rows affected

SELECT * FROM tableb;


JOB_ID
EMPLOYEE
CURRENT_DATE
SALARY


10102
Steven
1999-01-01 00:00:00
20000

10102
Bob
1999-01-01 00:00:00
20000

102033
David
1999-01-02 00:00:00
30000

102033
Charlie
1999-01-02 00:00:00
30000

102033
Keren
1999-01-02 00:00:00
30000


ROLLBACK;
INSERT INTO tableB (job_id, employee, current_date,salary)
SELECT a.job_id,
       TRIM(REGEXP_SUBSTR(a.employee_list, '[^,]+', 1, l.idx)),
       a.current_date,
       a.salary
FROM   tableA a
       CROSS APPLY(
         SELECT LEVEL AS idx
         FROM   DUAL
         CONNECT BY LEVEL <= LENGTH(employee_list) - LENGTH(REPLACE(employee_list,',')) + 1
       ) l
--WHERE  a.job_type = 'Managerial'
--AND    a.current_date = sysdate;
5 rows affected

SELECT * FROM tableb;


JOB_ID
EMPLOYEE
CURRENT_DATE
SALARY


10102
Steven
1999-01-01 00:00:00
20000

10102
Bob
1999-01-01 00:00:00
20000

102033
David
1999-01-02 00:00:00
30000

102033
Charlie
1999-01-02 00:00:00
30000

102033
Keren
1999-01-02 00:00:00
30000

JOB_ID	EMPLOYEE	CURRENT_DATE	SALARY
10102	Steven	1999-01-01 00:00:00	20000
10102	Bob	1999-01-01 00:00:00	20000
102033	David	1999-01-02 00:00:00	30000
102033	Charlie	1999-01-02 00:00:00	30000
102033	Keren	1999-01-02 00:00:00	30000