Split CLOB column based on new line – Oracle SQL

I have a table

table1

f_name     f_content
test1.txt  YL*1**50*1~
           RX*1~
           LR*2~
test2.txt  YL*1**49*1~
           EE*1~
           WW*2~

• f_content is CLOB
• f_name is varchar2 (4000)

I have written this SQL:

SELECT
    d.*,
    c.line_num,
    translate(substr(d.f_content, part1 + 1, part2 - part1), ' ~'
                                                                || CHR(10)
                                                                || CHR(13), ' ') line
FROM
    table1 d
    CROSS JOIN LATERAL (
        SELECT
            level   line_num,
            DECODE(level, 1, 0, regexp_instr(d.f_content, '~', 1, level - 1)) part1,
            DECODE(regexp_instr(d.f_content, '~', 1, level), 0, length(d.f_content), regexp_instr(d.f_content, '~', 1, level
            )) part2
        FROM
            dual
        CONNECT BY
            level <= regexp_count(d.f_content, '~ ')
    ) c;

My expected output is:

f_name     f_content        line_num    line
test1.txt  YL*1**50*1~      1           YL*1**50*1
           RX*1~
           LR*2~
test1.txt  YL*1**50*1~      2           RX*1
           RX*1~
           LR*2~
test1.txt  YL*1**50*1~      3           LR*2
           RX*1~
           LR*2~


test2.txt  YL*1**49*1~      1           YL*1**49*1
           EE*1~
           WW*2~
test2.txt  YL*1**49*1~      2           EE*1
           EE*1~
           WW*2~
test2.txt  YL*1**49*1~      3           WW*2
           EE*1~
           WW*2~

However in the output based on the SQL above, I am only getting line_num = 1.

How can I get the SQL code working so it gives all the lines?

Answer

You can use a hierarchical query without a JOIN condition :

select t1.*, level as line_num, 
       regexp_replace( regexp_substr( t1.f_content,'[^~]+', 1, level), '(^[[:space:]]+)' ) 
       as line
  from table1 t1
 connect by level <= regexp_count(f_content, '~')   
    and prior f_name = f_name
    and prior sys_guid() is not null

Demo