Oracle procedure with input parameter that performs multiple SUBSTR
You don't need a procedure, you can do this in a single SQL statement (though you could wrap that statement in a procedure if necessary):




insert into employee(employee_name)
with w as (select 'adam,alex,hales,jordan,jackob' names from dual)
select regexp_substr(names,'[^,]+', 1, level) from w
connect by regexp_substr(names, '[^,]+', 1, level) is not null;






select * from employee;



EMPLOYEE_ID | EMPLOYEE_NAME
----------: | :------------
          1 | adam         
          2 | alex         
          3 | hales        
          4 | jordan       
          5 | jackob       



dbfiddle here