Get String, Split By delimiter and inset to table Procedure in Oracle

Question

I have Names table , z_names_seq sequence for AutoGenerate IDs (in names table) and Trigger z_names_on_insert that uses z_names_seq for Generate ID.

--Create Names Table

CREATE TABLE z_names (ID number,
                      NAME VARCHAR2(200))

--Sequence For Names Table

CREATE SEQUENCE z_names_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1;

--Trigger For ID In Names Table

create or replace TRIGGER z_names_on_insert
    BEFORE INSERT ON z_names
    FOR EACH ROW
BEGIN
    SELECT z_names_seq.nextval
    INTO :new.ID
    FROM dual;
END;

AND Whats The Question:

Write the procedure that gets string value, separate it by delimiter and insert into z_names table (ID comes from trigger and NAME comes from input string split by comma ( , )).

Example Of Input and output:

input : john, jim, jack

output:

ID	NAME
1	john
2	jim
3	jack

Answer 1

Split the string into rows. Here's one option which shows how to do that. INSERT doesn't contain the ID column as it is populated by a trigger.

SQL> create or replace procedure p_ins (par_string in varchar2) is
  2  begin
  3    insert into z_names (name)
  4      select trim(regexp_substr(par_string, '[^,]+', 1, level))
  5        from dual
  6        connect by level <= regexp_count(par_string, ',') + 1;
  7  end;
  8  /

Procedure created.

SQL> exec p_ins('john,jim,jack');

PL/SQL procedure successfully completed.

SQL> select * From z_names;

        ID NAME
---------- --------------------
         1 john
         2 jim
         3 jack

SQL>