SQL语句Concatenation字符串拼接错误

预计阅读时间：7分钟老旧的Tuxedo服务，测试环境接收一种新报文，处理过程中报错，从应用日志看，提示的是ORA-01489，以前没见过的一种错误。

这是在Solaris平台下，使用Pro*C开发的Tuxedo应用，出错部分的代码模拟如下，就是使用||做字符串拼接的select语句，

EXEC SQL 
   select m1||m2||m3||m4||m5||m6||m7||m8||m9||m10||m11||m12||m13||m14||m15||m16||m16||m17||m18||m19||m20 
    into :area from tbl
    where a=:a and b=:b;

关于ORA-01489，oerr解释如下，字符串Concatenation操作结果集太长，超过了最大值，修改方法的建议是确保结果集不会超过最大值，

oerr ora 1489
01489, 00000, "result of string concatenation is too long"
// *Cause: String concatenation result is more than the maximum size.
// *Action: Make sure that the result is less than the maximum size.

这是什么限制？

MOS这篇文章《ORA-01489 When Running Query (Doc ID 2141406.1)》，给出了解释，

The issue is caused by exceeding concatenation limit of 4000 characters.这个出错，是由于超过了Concatenation操作4000字符的限制。

There is a maximum length limit for concatenation operations of varchar2 is 4k.对于Concatenation连接VARCHAR2类型操作的最大长度限制是4k。

As Database SQL Language Reference 11.2 and Database SQL Language Reference 12c states:

Oracle 11.2和12c的SQL参考手册指出：

The result of concatenating two character strings is another character string. If both character strings are of data type CHAR, then the result has data type CHAR and is limited to 2000 characters. If either string is of data type VARCHAR2, the result has data type VARCHAR2 and is limited to 4000 characters. If either argument is a CLOB, the result is a temporary CLOB. Trailing blanks in character strings are preserved by concatenation, regardless of the data types of the string or CLOB.

两个字符串类型变量的Concatenation操作结果，仍是字符串类型。如果这两个字符串类型都是CHAR，那么连接结果的数据类型，也是CHAR，因此最大限制是2000字符。如果有任何一个变量，数据类型是VARCHAR2，那么结果的变量类型，就是VARCHAR2，最大限制是4000字符。如果有任何一个变量，数据类型是CLOB，那么结果变量数据类型，就是一个临时的CLOB类型。字符串结尾的空格，会被保存到Concatenation结果，无论数据类型是字符串，还是CLOB。

给出的解决方案，也很明确，

Trim query to not exceed the limit of concatenation operations (4k)

让检索结果小于Concatenation操作限制

OR

Use to_clob() for each concatenated string.

对每个连接的字符串使用to_clob()，超过2k或4k的限制

模拟创建一张表，20个VARCHAR2(255)类型的字段，

create table TBL
(
 A   CHAR(1),
 B   CHAR(1),
 C1  VARCHAR2(255),
 C2  VARCHAR2(255),
 C3  VARCHAR2(255),
 C4  VARCHAR2(255),
 C5  VARCHAR2(255),
 C6  VARCHAR2(255),
 C7  VARCHAR2(255),
 C8  VARCHAR2(255),
 C9  VARCHAR2(255),
 C10  VARCHAR2(255),
 C11  VARCHAR2(255),
 C12  VARCHAR2(255),
 C13  VARCHAR2(255),
 C14  VARCHAR2(255),
 C15  VARCHAR2(255),
 C16  VARCHAR2(255),
 C17  VARCHAR2(255),
 C18  VARCHAR2(255),
 C19  VARCHAR2(255),
 C20  VARCHAR2(255)
);

插入测试数据，C1-C20总长度超过了4k，

SQL select length(c1||c2||c3||c4||c5||c6||c7||c8||c9||c10) + length(c11||c12||c13||c14||c15||c16||c16||c17||c18||c19||c20) length
  2  from tbl
  3  a='a' and b='b';
    LENGTH
----------
   5307

执行拼接操作，就会提示ORA-01489错误，

SQL select c1||c2||c3||c4||c5||c6||c7||c8||c9||c10||c11||c12||c13||c14||c15||c16||c16||c17||c18||c19||c20
2  from tbl
3  where a='a' and b='b';
from tbl
   *
ERROR at line 2:
ORA-01489: result of string concatenation is too long

要么||拼接的长度，小于4k，要么使用to_clob()对每个字符串，使用CLOB代替字符串类型操作。无论哪一种，都是需要改代码。

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，