title: Split CLOB column based on new line - Oracle SQL
body: <p>I have a table</p>
  
  <p>table1</p>
  
  <pre><code>f_name     f_content
  test1.txt  YL*1**50*1~
             RX*1~
             LR*2~
  test2.txt  YL*1**49*1~
             EE*1~
             WW*2~
  </code></pre>
  
  <ul>
  <li>f_content is CLOB</li>
  <li>f_name is varchar2 (4000)</li>
  </ul>
  
  <p>I have written this SQL:</p>
  
  <pre><code>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 &lt;= regexp_count(d.f_content, '~ ')
      ) c;
  </code></pre>
  
  <p>My expected output is:</p>
  
  <pre><code>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~
  </code></pre>
  
  <p>However in the output based on the SQL above, I am only getting line_num = 1. </p>
  
  <p>How can I get the SQL code working so it gives all the lines?</p>