-1

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?

Raj Mohan
  • 47
  • 1
  • 9
  • Kindly refer this link to the oracle docs https://docs.oracle.com/cd/B19306_01/server.102/b14237/limits001.htm#i287903 ,in the documentation it is mentioned as 4000 bytes.@Abra – Raj Mohan Dec 03 '20 at 07:02
  • Look here: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref732 _The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes_ – Abra Dec 03 '20 at 07:21

2 Answers2

1

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

In PL/SQL, the max length of the varchar2 is 32767.

You can pass the string of the mentioned length to the procedure.

Popeye
  • 35,427
  • 4
  • 10
  • 31