Oracle string to line

Select substr (T. RPT_ID,
INSTR (T. RPT_ID, ',', 1, C. LV) + 1,
INSTR (T. RPT_ID, ',', 1, C. LV + 1)-(INSTR (T. RPT_ID, ',', 1, C. LV) + 1 ))
AS RPT_ID
FROM (SELECT ',' | '85, '|', 'rpt_id,
LENGTH ('85, 86,87 '|', ')-NVL (LENGTH (REPLACE ('85, 86',', '), 0) CNT
From dual) t,
(Select level lv
FROM DUAL
Connect by level <= LENGTH ('85, '|', ')-NVL (LENGTH (REPLACE ('85,'), 0 )) c
Where t. cnt> = c. lv
Note: CNT indicates the number of characters in the string.
When the string is a field in the table, take the level <= maximum number
For example:
1). obtain the maximum number.
Select max (LENGTH (RPT_ID | ',')-
NVL (LENGTH (REPLACE (RPT_ID, ','), 0) INTO v_c
FROM DIM_AUDIT_TABLE @ sjmh_inter;
2). l convert to line
SELECT T.T _ NAME,
T.T _ NAME_COMM,
T.T _ COLUMN,
T.T _ COLUMN_COMM,
T. COMMENT_NL,
T. COMMENT_NL_TIME,
T. SEQ_USER_ID,
SUBSTR (T. RPT_ID,
INSTR (T. RPT_ID, '','', 1, C. LV) + 1,
INSTR (T. RPT_ID, '','', 1, C. LV + 1 )-
(INSTR (T. RPT_ID, '','', 1, C. LV) + 1) AS RPT_ID
FROM (SELECT A.T _ NAME,
A.T _ NAME_COMM,
A.T _ COLUMN,
A.T _ COLUMN_COMM,
A. COMMENT_NL,
A. COMMENT_NL_TIME,
A. SEQ_USER_ID,
'','' | A. RPT_ID | '','' RPT_ID,
LENGTH (A. RPT_ID | '','')-
NVL (LENGTH (REPLACE (A. RPT_ID, '',''), 0) CNT
FROM DIM_AUDIT_TABLE @ sjmh_inter
WHERE a. COMMENT_NL is not null) T,
(Select level lv from dual connect by level <= '| v_c |') C
Where c. LV <= T. CNT;

Note: If the above Code is remote code, (select level lv from dual connect by level <= '| v_c |') C. Do not use the dual table, you can use all_objects or user_objects instead. Otherwise, you can query the data. However, when you insert the queried SQL statement into a table, you can only insert one row.