14

When you create a procedure (or a function) in Oracle PL/SQL, you cannot specify the maximum length of the varchar2 arguments, only the datatype. For example

create or replace procedure testproc(arg1 in varchar2) is
begin
  null;
end;

Do you know the maximum length of a string that you can pass as the arg1 argument to this procedure in Oracle ?

user272735
  • 10,473
  • 9
  • 65
  • 96
Aurelio Martin Massoni
  • 1,706
  • 3
  • 14
  • 19

3 Answers3

16

In PL/SQL procedure it may be up to 32KB

Futher information here: http://it.toolbox.com/blogs/oracle-guide/learn-oracle-sql-and-plsql-datatypes-strings-10804

Gravstar
  • 1,071
  • 6
  • 10
  • 2
    Not quite, the real limit is 32767 bytes which is 32KB - 1. The correct answer is: http://stackoverflow.com/a/22060657/480894 – Roland Jul 07 '15 at 07:49
10

I tried with testproc( lpad( ' ', 32767, ' ' ) ) and it works.

With 32768 bytes it fails, so it's 32K - 1 bytes

Aurelio Martin Massoni
  • 1,706
  • 3
  • 14
  • 19
3

In PL/SQL the maximum size of VARCHAR2 datatype is 32767 bytes since 10gR2 (and probably earlier but I just checked the documentation upto that release).

The documentation references:

user272735
  • 10,473
  • 9
  • 65
  • 96
  • Just curious, do you know why not 32Kb instead of 32Kb -1 ? – Roland Jul 07 '15 at 07:49
  • @Roland No I don't know. Maybe it's a [null-terminated string](https://en.wikipedia.org/wiki/Null-terminated_string), maybe something else. Oh and please don't ask why it's 2^15-1 and not 2^16-1. Only Oracle knows. :) – user272735 Jul 07 '15 at 08:12
  • 1
    @Roland, it's because default 2-byte signed integer variable has range from -32768 to 32767. – General-Doomer Oct 16 '15 at 06:40