[SOLVED] How to get a string with more than 4000 characters as input to a Store Procedure in PLSQL?

How to get a string with more than 4000 characters as input to a Store Procedure in PLSQL?

I have a store procedure in oracle which takes string input through a varchar2 type input parameter,I believe that 4000 bytes is the max length we can pass through the parameter, i need to have more characters in my input, what would be a viable solution?

Solution

One option is to use CLOB datatype.

Though, are you sure in what you are saying? It is 11g, and no problem in passing parameter longer than 4000 characters:

SQL> create or replace procedure p_test (par_str in varchar2)
  2  is
  3    l_len number;
  4  begin
  5    l_len := length(par_str);
  6    dbms_output.put_line('length of l_len = ' || l_len);
  7  end;
  8  /

Procedure created.

SQL> set serveroutput on
SQL> declare
  2    l_val varchar2(5000);
  3  begin
  4    l_val := lpad('x', 5000, 'x');
  5    dbms_output.put_line('length of l_val = ' || length(l_val));
  6
  7    p_test(l_val);
  8  end;
  9  /
length of l_val = 5000
length of l_len = 5000

PL/SQL procedure successfully completed.

SQL>