Parsing rows from clob field

pl-sql_clob_parsing.sql

CREATE OR REPLACE Procedure get_rows_from_first_table
 IS
    cursor c_clob is
    select raw_content from first_table;
    raw_content CLOB;
    procedure read_lines
        (p_clob in out nocopy CLOB) is
        offset number := 1;
        amount number := 32767;
        len    number := dbms_lob.getlength(p_clob);
        lc_buffer varchar2(10000);
        i pls_integer := 1;
        TYPE tab_number IS TABLE OF VARCHAR2(10000);
        t_str tab_number := tab_number();
        
        first_num INTEGER NULL;
        second_num INTEGER NULL;
        section_name  VARCHAR2(600) NULL;
        account_name VARCHAR2(600) NULL;
        is_valid_row BOOLEAN NULL;
        j INTEGER;
        attrib CHAR(2) NULL;
        TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
        MY_ARRAY T_ARRAY_OF_VARCHAR;
    begin
        is_valid_row := false;
        if ( dbms_lob.isopen(p_clob) != 1 ) then
            dbms_lob.open(p_clob, 0);
        end if;
        while ( offset < len )
        loop
            if (instr(p_clob, chr(10), offset) = 0) then
                amount := len - offset + 1;
            else
                amount := instr(p_clob, chr(10), offset) - offset;
            end if;
            if ( amount = 0 ) then
                lc_buffer := '';
            else
                dbms_lob.read(p_clob, amount, offset, lc_buffer);
            end if;
            j := 0;
              FOR CURRENT_ROW IN (
                with test as    
                  (select lc_buffer from dual)
                  select regexp_substr(lc_buffer, '[^'||chr(9)||']+', 1, rownum) SPLIT
                  from test
                  connect by level <= length (regexp_replace(lc_buffer, '[^'||chr(9)||']+'))  + 1)
              LOOP
                if j = 0 then
                    if REGEXP_LIKE(CURRENT_ROW.SPLIT, '^[[:digit:]]+$') then
                        is_valid_row := true;
                        if LENGTH(CURRENT_ROW.SPLIT) = 3 then
                            first_num := CURRENT_ROW.SPLIT;
                            second_num := null;
                        elsif LENGTH(CURRENT_ROW.SPLIT) = 5 then
                            second_num := CURRENT_ROW.SPLIT;
                        end if;
                    else
                        is_valid_row := false;
                    end if;
                elsif j = 1 then
                    if is_valid_row then
                        if LENGTH(CURRENT_ROW.SPLIT) > 0 then
                            if not second_num is null then
                                account_name := CURRENT_ROW.SPLIT;
                            elsif not first_num is null then
                                section_name := CURRENT_ROW.SPLIT;
                            end if;
                        end if;
                    end if;
                elsif j = 2 then
                    if LENGTH(CURRENT_ROW.SPLIT) = 1 then
                        attrib := SUBSTR(CURRENT_ROW.SPLIT, 1, 1);
                    else
                        attrib := null;
                    end if;
                    if not first_num is null then
                        if not second_num is null then
                            if not section_name is null then
                                if not section_name is null then
                                    INSERT INTO second_table VALUES (second_table_seq.NEXTVAL, first_num, second_num, section_name, account_name, attrib);
--                                    DBMS_OUTPUT.PUT_LINE(first_num||'|'||second_num||'|'||section_name||'|'||account_name||'|'||attrib);
                                    second_num := null;
                                    account_name := null;
                                end if;
                            end if;
                        end if;
                    end if;
                end if;
                j := j + 1;
                MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
              END LOOP;
            t_str.DELETE;
            i := i + 1;
            if (instr(p_clob, chr(10), offset) = len) then
                lc_buffer := '';
            end if;

            offset := offset + amount + 1;
          end loop; 
     if ( dbms_lob.isopen(p_clob) = 1 ) then
        dbms_lob.close(p_clob);
      end if; 
    exception
      when others then
         dbms_output.put_line('Error : '||sqlerrm);
    end read_lines;
BEGIN
    dbms_output.put_line('-----------');
    open c_clob;
    loop
        fetch c_clob into raw_content;
        exit when c_clob%notfound;
        dbms_output.put_line('1');
        read_lines(raw_content);
    end loop;
    close c_clob;
    commit;
END;