Starting from Oracle 12c you could use JSON_TABLE
and JSON_ARRAY
:
CREATE TABLE tab(Name, Project, Error) AS
SELECT 108,'test' ,'Err1, Err2, Err3' FROM dual UNION
SELECT 109,'test2','Err1' FROM dual;
And query:
SELECT *
FROM tab t
OUTER APPLY (SELECT TRIM(p) AS p
FROM JSON_TABLE(REPLACE(JSON_ARRAY(t.Error), ',', '","'),
'$[*]' COLUMNS (p VARCHAR2(4000) PATH '$'))) s;
Output:
+------------------------------------------+
¦ Name ¦ Project ¦ Error ¦ P ¦
+------+---------+------------------+------¦
¦ 108 ¦ test ¦ Err1, Err2, Err3 ¦ Err1 ¦
¦ 108 ¦ test ¦ Err1, Err2, Err3 ¦ Err2 ¦
¦ 108 ¦ test ¦ Err1, Err2, Err3 ¦ Err3 ¦
¦ 109 ¦ test2 ¦ Err1 ¦ Err1 ¦
+------------------------------------------+