PLS-00172: string literal too long error occurs when an Oracle PL/SQL code contains a string literal that exceeds the maximum allowed character length of 32767. The Oracle PL/SQL code includes a string variable that is assigned a string length greater than 32767 characters. The oracle string cannot store a string that is longer than the string data type’s maximum length. The string literal size should be truncated within the string’s allowable length. Alternatively, the data type should be changed from string to CLOB. The error PLS-00172: string literal too long will be resolved.

The Oracle string data type can hold a string with a maximum length of 32767 characters or 4000 bytes. If you assign a string literal with a length greater than the maximum allowed limit in the PL/SQL code, the string literal will not be stored in the Oracle string variable. Instead, Oracle will throw an error stating that the assigned string literal is longer than the expected maximum length. Oracle also allows you to store a string that is longer than the maximum length of the string data type using the CLOB data type.



The Problem

The PL/SQL code block can create a string variable that can hold a string literal up to 32767 characters or 4000 bytes in length. If a string literal with a length greater than 32767 characters is assigned to a string variable, the string variable cannot store the value. Oracle will throw an error in this case. The assigned string literal length is greater than the expected string literal length. This error will be produced by the following code sample, and the Oracle error will be displayed as shown below.

declare
  my_message  varchar2;
begin
  my_message := '-------A string literal with length more than 32767 characters------';

end;
/

Error

Error report -
ORA-06550: line 4, column 17:
PLS-00172: string literal too long
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Solution 1

If the length of the string literal exceeds the maximum allowed length of 32767 characters or 4000 bytes, reduce the length of the string literal and assign the value to the string variable. This will fix the problem. The literal size of the string cannot be greater than the string’s maximum size. The error can be fixed by reducing the length of the error-prone string.

Error

declare
  my_message  varchar2;
begin
  my_message := '-------A string literal with length more than 32767 characters------';

end;
/

Solution

declare
  my_message  varchar2;
begin
  my_message := '-------A string literal with length less than 32767 characters------';

end;
/


Solution 2

If the string literal is more than the string maximum allowed size, the string should be splitted into multiple string with size less than the allowed size. The strings can be concadinated into a single CLOB string. The following code shows how to concatenate the large string.

declare
  my_message  clob;
  temp_message   clob;
begin
  my_message := 'String literal with length less than 32767 characters';
  temp_message  := 'String literal with length less than 32767 characters';
  dbms_lob.append(my_message,temp_message);
  temp_message  := 'String literal with length less than 32767 characters';
  dbms_lob.append(my_message,temp_message);
  DBMS_OUTPUT.PUT_LINE(my_message);
end;
/


Solution 3

The another way of appending the strings to make a large string is using the CLOB concatenation. The following example will show how to append the string before inserting into the CLOB data type column. The three large size strings are appended and stored in a table CLOB datatype column

CREATE TABLE test
(
     name VARCHAR2(90),
     htmlcode CLOB
);

declare
  my_message1  clob;
  my_message2   clob;
  my_message3   clob;
begin
  my_message1 := 'String literal with length less than 32767 characters';
  my_message2  := 'String literal with length less than 32767 characters';
  my_message3  := 'String literal with length less than 32767 characters';

  insert into test (name, htmlcode)
  select 'sample data', my_message1 || my_message2 || my_message3 from dual;
end;
/



Leave a Reply