How to pass a parameter of CLOB type to Oracle stored procedure which is having 100k+ length string?

Question

 Create procedure p(p1 clob) as 
(
##code goes here..
);

exec p('100k+ length string...');

When I tried above procedure with 100k+ length string it is throwing ORA-20002: -20002:ORA-20002: -6502:ORA-06502: PL/SQL: numeric or value error\nORA-06512: at

How can we pass the value to the stored procedure? Do we need to increase the db_block_size to increase the capacity of CLOB datatype?

Answer 1

You can pass the clob to the procedure just like other data types (on db<>fiddle):

create or replace procedure p (p1 clob) as 
begin 
    dbms_output.put_line ('clob len='||length (p1)); 
end;
/
declare 
    c clob := '123';
begin
    for i in 1..4 loop
        dbms_lob.append (c, rpad ('A', 32767, 'A')); end loop; 
    p (c);
end;
/

clob len=131071

Answer 2

I think you are mistaken what a CLOB datatype can store with the procedure put_line of dbms_output

DBMS_OUTPUT.PUT_LINE ( item IN VARCHAR2);

The item is a varchar2 object. So, if you want to print the output of a clob greater than the limit of a varchar, you need to create a different kind of code. In my case, I use always the below code to print a clob column:

create or replace procedure print_clob_to_output (p_clob in clob)
        is
          l_offset     pls_integer := 1;
          l_chars      pls_integer;
        begin
            loop
                exit when l_offset > dbms_lob.getlength(p_clob);
                l_chars := dbms_lob.instr(p_clob, chr(10), l_offset, 1);
                if l_chars is null or l_chars = 0 then
                    l_chars := dbms_lob.getlength(p_clob) + 1;
                end if;
                dbms_output.put_line(dbms_lob.substr(p_clob, l_chars - l_offset, l_offset));
                l_offset := l_chars + 1;
            end loop;
        end print_clob_to_output;