Oracle string separated by commas to multiple rows in a table

Question

I have a lookup table as follows:

id descr
1   maths
2   reading
3   science
4   history
5   chemistry
6   physics

I have another table, cases, with the following data

cases
id  name    subjects    
1   ABC     maths,reading
2   DEF     maths, chemistry
3   GHI     physics,chemistry

I have a stored procedure that opens a cursor on the cases table and iterates over each case and inserts the value into the base table and child tables. I have a cases_subjects table which needs entry as follows:

caseId  Subject
1       1   
1       2
2       1
2       5
3       6
3       5

I have a stored procedure like below. It has so many other validations so using SP is the only option. What is the best way to get this done with the below code?

create or replace PROCEDURE INSERT_CASES AS 
v_subj number;
cursor c1 is select * from  cases;
begin
       for t_case in c1
        if t_case.subjects is not null then
            --use INSTR or call function to convert one row from cases to many rows in cases_subjects
        else
            --do nothing
        end if;
       end loop;       
       commit;
exception
when others then 
    log_error(0,  'INSERT_case - exception outside' || SQLERRM || ' code ' || SQLCODE, 1);
    commit;
end;

Any input is highly appreciated.

Answer 1

You do not need to split the string and can join the table on delimited sub-string matches using LIKE:

SELECT c.id AS caseid,
       l.id AS subjectid
FROM   cases c
       INNER JOIN lookup l
       ON (','|| c.subjects || ',' LIKE '%,' || l.descr || ',%')

Which, for the sample data:

CREATE TABLE lookup (id, descr) AS
SELECT 1, 'maths'     FROM DUAL UNION ALL
SELECT 2, 'reading'   FROM DUAL UNION ALL
SELECT 3, 'science'   FROM DUAL UNION ALL
SELECT 4, 'history'   FROM DUAL UNION ALL
SELECT 5, 'chemistry' FROM DUAL UNION ALL
SELECT 6, 'physics'   FROM DUAL;

CREATE TABLE cases (id, name, subjects) AS
SELECT 1, 'ABC', 'maths,reading'     FROM DUAL UNION ALL
SELECT 2, 'DEF', 'maths,chemistry'  FROM DUAL UNION ALL
SELECT 3, 'GHI', 'physics,chemistry' FROM DUAL;

CREATE TABLE case_subjects (caseid NUMBER, subjectid NUMBER);

Outputs:

CASEID	SUBJECTID
1	1
1	2
2	1
2	5
3	5
3	6

If you want it as an insert then you do not need a cursor and can use INSERT ... SELECT:

INSERT INTO case_subjects(caseid, subjectid)
SELECT c.id,
       l.id
FROM   cases c
       INNER JOIN lookup l
       ON (','|| c.subjects || ',' LIKE '%,' || l.descr || ',%')

db<>fiddle here