CODEWITHSUNDEEP
oracle-databaseplsql
Aurelio Martin Massoni
Aurelio Martin Massoni

Reputation: 1706

What is the size limit for a varchar2 PL/SQL subprogram argument in Oracle?

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 ?

Upvotes: 14

Views: 59765

Answers (3)

user272735
user272735

Reputation: 10648

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:

Upvotes: 3

Aurelio Martin Massoni
Aurelio Martin Massoni

Reputation: 1706

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

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

Upvotes: 10

Gravstar
Gravstar

Reputation: 1091

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

Upvotes: 16

Related Questions