PLS-00172: string literal too long

PLS-00172: string literal too long
Cause: The string literal was longer than 32767 bytes.
Action: Use a string literal of at most 32767 bytes.

"PLS-00172: string literal too long" is a very general issue while concatenating strings in SQL or in PL/SQL. We can simply reproduce "PLS-00172" by trying to store a huge XML in a CLOB, like

declare
  my_xml  clob;
begin
  my_xml := '<?xml version="1.0"?>
<purchaseorder orderdate="1999-10-20">
  <shipto country="us">
  .
  .
  Some 50000+ 
  .
  .
      <shipdate>1999-05-21</shipdate>
    </item>
  </items>
</purchaseorder>';

    update mytable set purchaseorder = my_xml
    where id = 87625;
end;
/

We can even try to put the string literal inside to_clob() but we will still face "PLS-00172: string literal too long"

declare
  my_xml  clob;
begin
  my_xml := to_clob('<?xml version="1.0"?>
<purchaseorder orderdate="1999-10-20">
  <shipto country="us">
  .
  .
  Some 50000+ 
  .
  .
      <shipdate>1999-05-21</shipdate>
    </item>
  </items>
</purchaseorder>');

    update mytable set purchaseorder = my_xml
    where id = 87625;
end;
/

The reason for this error is not the size of my_xml, it is clob and can hold a very large string than being passed in above example. The issue is with string literal i.e. a single string between quotes with which we are trying to set clob variable.

There is following limit on the size of string literals.
- 4000 bytes in SQL
- 32k in PLSQL

As we cannot have a string literal over 4000 bytes in SQL and 32K bytes in PL/SQL so we need to build up the clob by appending/concatenating many small string literals instead of one large on, like

declare
  my_xml  clob;
  t_xml   clob;
begin
  my_xml := '32K Bytes Character String';
  t_xml  := 'next 32K Bytes Character String';
  dbms_lob.append(my_xml,t_xml);
  t_xml  := 'next 32K Bytes Character String';
  .
  .
  .
  dbms_lob.append(my_xml,t_xml);
  update mytable set purchaseorder = my_xml
  where id = 87625;
end;
/

I hope this post has solved your problem and you have enjoyed reading this post.


Related Posts:
- ORA-01489: result of string concatenation is too long
- ORA-06502: PL/SQL: numeric or value errorstring
- Oracle: New String Aggregation Techniques
- Oracle: Nth Record from Comma Separated String