0

I have a table Employee with the columns employee_id as type number, and employee_name as type varchar.

I need a procedure that takes an input string to insert employees into the table. The procedure should read and analyze the string which will include multiple names separated by commas.

Example:

'adam,alex,hales,jordan,jackob'

Each name should be extracted up until the comma and inserted into the employees table. So basically using the input string above I should have the names inserted as in the following result:

 employee_id | employee_name
-------------+--------------
 1           | adam
 2           | alex
 3           | hales
 4           | jordan
 5           | jackob

Any solution is appreciated.

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.