Splitting a clob into rows

I’ve used this tool for a wide variety of other parsing projects. One of the interesting tuning techniques I used was to pull the clob apart into 32K varchar2 chunks.
It is possible to split the clob directly using the DBMS_LOB package or through the overloaded SQL functions; but clobs are expensive objects. Varchar2 variables on the other hand are relatively light weight making the sub-parsing within them much faster. Doing this does take a little bit of care though to make sure the chunks don’t accidentally split a line in two.

I make a few assumptions in the code below. I assume most lines will be more than 32K long. If they are, I will split into fixed length lines until I either run out of data or reach a delimiter. I also assume I’ll be working with single byte characters. If you use multibyte character sets, there are two places in the code, you will want to change. These lines are indicated with comments.

The returned VCTAB type is a simple table collection type.

CREATE OR REPLACE TYPE VCTAB as table of varchar2(4000)

CREATE OR REPLACE FUNCTION split_clob(
    p_clob        IN CLOB,
    p_delimiter   IN VARCHAR2 DEFAULT CHR(10))
    RETURN vctab
    PIPELINED
IS
    --                    .///.
    --                   (0 o)
    ---------------0000--(_)--0000---------------
    --
    --  Sean D. Stuber
    --  sean.stuber@gmail.com
    --
    --             oooO      Oooo
    --------------(   )-----(   )---------------
    --             \ (       ) /
    --              \_)     (_/

    c_line_limit    CONSTANT INTEGER := 4000; -- for single byte characters
    --c_line_limit    CONSTANT INTEGER := 1000; -- for multibyte characters
    
    -- Chunk limit is slightly less than a full varchar2 limit to allow for
    -- dbms_lob.substr return length limits with multibyte character sets
    -- For single byte characters, this is still sufficient.

    c_chunk_limit   CONSTANT INTEGER := 8 * c_line_limit;

    v_clob_length            INTEGER;
    v_clob_index             INTEGER;
    v_chunk                  VARCHAR2(32767);
    v_chunk_end              INTEGER;
    v_chunk_length           INTEGER;
    v_chunk_index            INTEGER;
    v_delim_len              INTEGER := LENGTH(p_delimiter);
    v_line_end               INTEGER;
BEGIN
    v_clob_length := DBMS_LOB.getlength(p_clob);
    v_clob_index := 1;

    WHILE v_clob_index <= v_clob_length
    LOOP
        --
        -- Pull one chunk off the clob at a time and process it.
        -- This is because it's MUCH faster to use built in functions
        -- on a varchar2 type than to use dbms_lob functions on a clob.
        --
        v_chunk := DBMS_LOB.SUBSTR(p_clob, c_chunk_limit, v_clob_index);

        IF v_clob_index > v_clob_length - c_chunk_limit  -- for single byte
        --IF v_clob_index > v_clob_length - length(v_chunk) -- for multibyte
        THEN
            -- If we walked off the end the clob,
            -- then the chunk is whatever we picked up at the end
            -- delimited or not.
            v_clob_index := v_clob_length + 1;
        ELSE
            -- Find the last delimiter in the chunk, mark that as the end.
            v_chunk_end := INSTR(v_chunk, p_delimiter, -1);

            IF v_chunk_end = 0
            THEN
                -- If there aren't any delimiters in a chunk
                -- then return a slightly smaller piece of the chunk
                -- to avoid accidentally splitting a delimiter
                -- that spans two chunks.
                -- This would cause part of the delimiter
                -- to be returned in the last line
                -- of one chunk, and the rest
                -- in the first line of the next chunk.
                -- To avoid the possibility,
                -- simply reduce the chunk by one line.
                v_chunk := SUBSTR(v_chunk, 1, c_chunk_limit - c_line_limit);
                v_clob_index := v_clob_index + c_chunk_limit - c_line_limit;
            ELSE
                -- If there are delimiters (this is the expected condtion)
                -- then pull the chunk up to and including the delimiter.
                v_chunk := SUBSTR(v_chunk, 1, v_chunk_end + v_delim_len - 1);
                v_clob_index := v_clob_index + v_chunk_end + v_delim_len - 1;
            END IF;
        END IF;

        --
        --  Given a varchar2 chunk split it into lines
        --
        v_chunk_index := 1;
        v_chunk_length := LENGTH(v_chunk);

        WHILE v_chunk_index <= v_chunk_length
        LOOP
            v_line_end := INSTR(v_chunk, p_delimiter, v_chunk_index);

            IF v_line_end = 0 OR (v_line_end - v_chunk_index) > c_line_limit
            THEN
                PIPE ROW (SUBSTR(v_chunk, v_chunk_index, c_line_limit));
                v_chunk_index := v_chunk_index + c_line_limit;
            ELSE
                PIPE ROW (SUBSTR(v_chunk,
                                 v_chunk_index,
                                 v_line_end - v_chunk_index));
                v_chunk_index := v_line_end + v_delim_len;
            END IF;
        END LOOP;
    END LOOP;

    RETURN;
EXCEPTION
    WHEN no_data_needed
    THEN
        NULL;
END split_clob;
/