SQL语句Concatenation字符串拼接错误

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 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的个人杂货铺,

SQL语句Concatenation字符串拼接错误
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> SQL语句Concatenation字符串拼接错误


 上一篇
探究外键为何要建索引? 探究外键为何要建索引?
预计阅读时间:22分钟 即将上线的一个新系统,性能测试的时候,一些操作非常慢,经过分析,发现有些SQL检索的条件,未建索引,而且这些字段是表的外键,加了索引,除了会提高这条SQL执行效率外,还会解决一些隐藏的问题,比如表级锁,其实《Orac
下一篇 
一个shell解析文件的需求 一个shell解析文件的需求
预计阅读时间:7分钟 由于Oracle没有MySQL或EDB慢日志这种机制,因此最近有一个运维的需求,需要解析应用的日志,进行SQL语句执行时间的统计,进而进行监控。 每个应用集群中的节点,会生成自己的处理日志,日志采用循环复写的机制,单个