clob-table-function.sql

CREATE OR REPLACE TYPE clob_rec AS OBJECT (
  num_characters number,
  the_clob clob
);

CREATE OR REPLACE TYPE clob_tbl AS TABLE OF clob_rec;


select * FROM TABLE(clob_tbl_fn(30000));

CREATE OR REPLACE FUNCTION clob_tbl_fn(p_size in number)
RETURN clob_tbl PIPELINED

AS
    l_clob  clob;
    l_loops number := 1;
    l_chunk_size constant number  := 30000;
    l_final_chunk number;
begin
    l_loops := ceil(p_size/l_chunk_size)-1;

    for i in 1..l_loops loop
        l_clob := l_clob || rpad('x',l_chunk_size,'x');
    end loop; --i

    l_final_chunk := p_size - (l_loops * l_chunk_size);
    l_clob := l_clob || rpad('x',l_final_chunk,'x');

    PIPE ROW(clob_rec(length(l_clob), l_clob));
END;
/

select * FROM TABLE(clob_tbl_fn2(42000));




CREATE OR REPLACE FUNCTION clob_tbl_fn2(
    p_start_size in number,
    p_increment_size in number default 0,
    p_increments in number default 1)
RETURN clob_tbl PIPELINED

AS
    l_clob  clob;
    l_loops number := 1;
    l_chunk_size constant number  := 30000;
    l_final_chunk number;
    l_updated_size number := p_start_size;
begin
    for i in 1..p_increments loop
        l_loops := ceil(l_updated_size/l_chunk_size)-1;
        l_clob := null;

        for j in 1..l_loops loop
            l_clob := l_clob || rpad('x',l_chunk_size,'x');
        end loop; --j

        l_final_chunk := l_updated_size - (l_loops * l_chunk_size);
        l_clob := l_clob || rpad('x',l_final_chunk,'x');

        PIPE ROW(clob_rec(length(l_clob), l_clob));

        l_updated_size := l_updated_size + p_increment_size;
    end loop; --i
END;
/

select * FROM TABLE(clob_tbl_fn2(300000,100000,10));