SQL & PL/SQL

CREATE TABLE ref_table (
    ref_id  NUMBER(10),
    code    VARCHAR2(50),
    val     VARCHAR2(50),
    constraint pk_ref_table primary key(ref_id)
);

insert into ref_table values(1,'maker','E_maker');
insert into ref_table values(2,'checker','E_checker');
insert into ref_table values(3,'sme','E_sme');

create table data_table ( id NUMBER(10),
    e_id VARCHAR2(50),
    maker VARCHAR2(100),
    checker VARCHAR2(100),
    sme VARCHAR2(100)
);

INSERT INTO data_table VALUES (
    1,
    11,
    'owner_fn,owner_ln;owner_fn_2,owner_ln_2',
    'owner_checker',
    'sme1,sme_ln1;sme2,sme_ln2'
);

CREATE TABLE org_table (
    e_id    NUMBER(10),
    ref_id  NUMBER(10),
    CONSTRAINT pk_org_table PRIMARY KEY ( e_id ),
    CONSTRAINT fk_org_table_ref_id FOREIGN KEY ( ref_id )
        REFERENCES ref_table ( ref_id )
);

ref_table -> This table is for the reference data. data_table -> This is the table that contains the actual data. org_table -> This is the table which I need to insert the data from data_table and ref_table.
My attempt:

MERGE INTO org_table ot USING ( SELECT
    e_id,
    regexp_substr(maker, '[^;]+', 1, level)             maker,
    regexp_substr(checker, '[^;]+', 1, level)           checker,
    regexp_substr(sme, '[^;]+', 1, level)               sme
FROM
    data_table
CONNECT BY e_id = PRIOR e_id
           AND PRIOR sys_guid() IS NOT NULL
           AND level <= regexp_count(maker, ';') + 1
           AND level <= regexp_count(checker, ';') + 1
           AND level <= regexp_count(sme, ';') + 1 ORDER BY E_ID ) S
  on ( ot.e_id = s.e_id )
WHEN NOT MATCHED THEN
INSERT (
    e_id,
    ref_id )
VALUES
    ( s.e_id,
      s.ref_id );

Tool used : SQL Developer version 20.4
Problem that I am facing:

SELECT
    e_id,
    regexp_substr(maker, '[^;]+', 1, level)             maker,
    regexp_substr(checker, '[^;]+', 1, level)           checker,
    regexp_substr(sme, '[^;]+', 1, level)               sme
FROM
    data_table
CONNECT BY e_id = PRIOR e_id
           AND PRIOR sys_guid() IS NOT NULL
           AND level <= regexp_count(maker, ';') + 1
           AND level <= regexp_count(checker, ';') + 1
           AND level <= regexp_count(sme, ';') + 1 ORDER BY E_ID 

This query should give me the below result like whenever in the columns maker, checker or some contains ; then it should insert a new record for the same e_id but this is not inserting into the new line.
And then, I want to insert the record into the org_table with e_id from data_table and ref_id from ref_table and the output should be like from the ref_table 1 is for maker column, 2 is for checker column and 3 is for some.
Expected Output:

+------+--------+--+
| e_id | ref_id |  |
+------+--------+--+
|   11 |      1 |  |
|   11 |      1 |  |
|   11 |      2 |  |
|   11 |      3 |  |
|   11 |      3 |  |
+------+--------+--+

1 - It came twice because there were two maker for the same e_id. So, from ref_table 1 is the id for the maker.
2 - It came only once because there was one checker for the same e_id. So, from ref_table 2 is the id for the checcker.
3 - It came thrice because there was two some for the same e_id. So, from ref_table 3 is the id for the sme.
How I will be able to join the columns from ref_table and data_table column value to get the desired result.

I have also asked the same question on Stackoverflow.