ORA-01489： Oracle Database 11g企业版版本中字符串连接的结果太长11.2.0.4.0-64位生产

Question

我在Oracle Database 11g Enterprise Edition 11.2.0.4.0-64位生产、PL/SQL 11.2.0.4.0-生产、CORE 11.2.0.4.0生产、TNS for Linux 11.2.0.4.0-生产、NLSRTL 11.2.0.4.0-生产中运行此查询

  CREATE OR REPLACE FORCE VIEW "IOT"."V_USERS_AND_RIGHTS" ("USER_PRIMARY_UNIT", "LOGIN", "FIRST_NAME", "LAST_NAME", "UNIT_ROLE") AS 
  SELECT "USER_PRIMARY_UNIT","LOGIN","FIRST_NAME","LAST_NAME","UNIT_ROLE"
FROM (
SELECT user_primary_unit,login, first_name,  last_name,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(rights,' / '))
       KEEP (DENSE_RANK LAST ORDER BY curr),' / ') AS UNIT_ROLE
      FROM  
        (SELECT  login,
              first_name,  
              last_name,
              user_primary_unit,
              rights,
              ROW_NUMBER() OVER (PARTITION BY login ORDER BY rights) AS curr,
              ROW_NUMBER() OVER (PARTITION BY login ORDER BY rights) -1 AS prev
        FROM  (select   member0_.login,  member0_.first_name first_name, unit2.unit_name user_primary_unit,  member0_.last_name last_name,
                        CONCAT(CONCAT(unit.unit_name, ' - '), role3_.role_name) rights 
 from
  IOT_DEVICES.t_member member0_
 inner join  IOT_DEVICES.t_user member0_1_    on member0_.member_id=member0_1_.user_id
 inner join  IOT_DEVICES.t_playable_role playedrole1_    on member0_.member_id=playedrole1_.user_id
 inner join  IOT_DEVICES.t_unit_role unitrole2_    on playedrole1_.unit_role_id=unitrole2_.unit_role_id
 inner join  IOT_DEVICES.t_role role3_    on unitrole2_.role_id=role3_.role_id
 inner join  IOT_DEVICES.t_unit unit    on unitrole2_.unit_id=unit.unit_id
 inner join  IOT_DEVICES.t_unit unit2    on unit2.unit_id=member0_1_.primary_unit_id
 where    current_date between playedrole1_.start_date and playedrole1_.end_date
 order by unit.unit_name
  ))
GROUP BY login, first_name,  last_name, user_primary_unit
CONNECT BY prev = PRIOR curr AND login = PRIOR login
START WITH curr = 1
)
ORDER BY user_PRIMARY_UNIT, FIRST_NAME, LAST_NAME;

但我有一个错误：

ORA-01489: result of string concatenation is too long
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.

即使我设置了所有这些变量：

SET LONG 2000000
SET pagesize 50000
set longchunk 1000
set linesize 1000

Answer 1

该错误与sqlplus设置无关，而是与Oracle DB中SQL引擎中的varchar2大小限制有关。对于11g，限制为4000字节。

处理查询时，其中一个串联会导致字符串长度超过限制。

乍一看

... LTRIM(MAX(SYS_CONNECT_BY_PATH(rights,' / ')) ...

和

... CONCAT(CONCAT(unit.unit_name, ' - '), role3_.role_name) rights ...

可能会导致这个问题。要么你对一个单位有很多权利，要么你有很深的层次结构。