Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (col1) as
select 'VALUE1
VALUE2

VALUE3
VALUE4' from dual
1 rows affected

select 
        level,
        case 
            when regexp_substr(a.col_trimmed_value, '[^' || CHR(10) || ']+',  1, level) is not null
                THEN substr(regexp_substr(a.col_trimmed_value, '[^' || CHR(10) || ']+',  1, level), 1, 200)
            ELSE '.'
            END as ROW_VALUE
from (
    select
        col1              as col_original_value
        ,replace(rtrim(ltrim(replace(col1,CHR(10),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#',CHR(10)) as col_trimmed_value
    from
        table1
    )a
connect by level <=  length ( a.col_trimmed_value ) - length(replace( a.col_trimmed_value, CHR(10)) ) + 1 
;


LEVEL
ROW_VALUE


1
VALUE1

2
VALUE2

3
VALUE3

4
VALUE4

5
.


select 
        level,
        case 
            when regexp_substr(a.col_trimmed_value, '(.*?)(' || CHR(10) || '|$)', 1, level, null, 1) is not null
                THEN substr(regexp_substr(a.col_trimmed_value, '(.*?)(' || CHR(10) || '|$)', 1, level, null, 1), 1, 200)
            ELSE '.'
            END as ROW_VALUE
from (
    select
        col1              as col_original_value
        ,replace(rtrim(ltrim(replace(col1,CHR(10),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#',CHR(10)) as col_trimmed_value
    from
        table1
    )a
connect by level <=  length ( a.col_trimmed_value ) - length(replace( a.col_trimmed_value, CHR(10)) ) + 1 
;


LEVEL
ROW_VALUE


1
VALUE1

2
VALUE2

3
.

4
VALUE3

5
VALUE4


select 
        level,
        coalesce(
          substr(regexp_substr(a.col_trimmed_value, '(.*?)(' || CHR(10) || '|$)', 1, level, null, 1), 1, 200),
          '.')
        as ROW_VALUE
from (
    select
        col1              as col_original_value
        ,replace(rtrim(ltrim(replace(col1,CHR(10),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#'),'#s1p@2l3t#',CHR(10)) as col_trimmed_value
    from
        table1
    )a
connect by level <=  length ( a.col_trimmed_value ) - length(replace( a.col_trimmed_value, CHR(10)) ) + 1 
;


LEVEL
ROW_VALUE


1
VALUE1

2
VALUE2

3
.

4
VALUE3

5
VALUE4