PLS-00215: String length constraints must be in range (1 .. 32767)

PLS-00215: String length constraints must be in range (1 .. 32767) error occurs when the varchar2 datatype length is not set or an invalid length is specified in the string variable declaration in PL/SQL Oracle code block. The varchar2 data type is used to declare string variables by specifying the string variable’s maximum character length. When declaring a string variable with varchar2, an error PLS-00215: String length constraints must be in range (1 .. 32767) will be thrown if the string maximum length is not specified or an invalid maximum length value is specified.

In the PL/SQL oracle code block, the varchar data type is used to declare a string variable. The maximum string length used to store a string literal should be specified for the varchar2 data type. If the maximum string length is not specified, varchar2 will not be able to allocate memory to store the string value in the string variable. The error message will be displayed.

A varchar2 string variable could not be created if the varchar2 data type was specified with an invalid string length. The invalid string length value cannot be used to create or allocate memory for the string value. If an invalid string length is assigned in the varchar2 variable declaration, Oracle will throw an error.



The Problem

The oracle PL/SQL block of code in the following example creates a varchar2 data type variable without specifying a string variable length value. The error will be thrown if the varchar2 data type variable is not specified with a range value. When declaring and assigning a string value to a variable, the varchar2 datatype expects a range value.

declare
  my_message  varchar2;
begin
  my_message := 'a';
end;
/

Error

ORA-06550: line 2, column 15:
PLS-00215: String length constraints must be in range (1 .. 32767)
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Solution 1

If the varchar2 variable is not specified with the string length range, the error will be shown in the oracle PL/SQL code. Check the varchar2 variable declaration in the PL/SQL code block. If any varchar2 variable is declared without any string length range, specify the string length range in the varchar2 variable. This will resolve the error.

Error

declare
  my_message  varchar2;
begin
  my_message := 'a';
end;
/

Solution

declare
  my_message  varchar2(10);
begin
  my_message := 'a';
end;
/


Solution 2

The varchar2 variable can have a maximum length of 32767 characters. Oracle could not create the varchar2 variable if it was declared with a string length range that exceeded the maximum allowed string length range. If the varchar2 range contains more than 32767 characters, Oracle will throw an error.

Error

declare
  my_message  varchar2(32800);
begin
  my_message := 'a';
end;
/

Solution

declare
  my_message  varchar2(32767);
begin
  my_message := 'a';
end;
/

Output

PL/SQL procedure successfully completed.


Solution 3

The error will be thrown if the varchar2 variable is declared with an invalid sting length value. If the range value in the varchar2 is zero or a negative number, the error will be thrown because it is an invalid range value. The varchar2 data type’s string range value should be between 1 and 32767.

Error

declare
  my_message  varchar2(0);
begin
  my_message := 'a';
end;
/

Solution

declare
  my_message  varchar2(30);
begin
  my_message := 'a';
end;
/

Output

PL/SQL procedure successfully completed.



Leave a Reply