I have this query running in a Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, PL/SQL Release 11.2.0.4.0 - Production, CORE 11.2.0.4.0 Production, TNS for Linux: Version 11.2.0.4.0 - Production, NLSRTL Version 11.2.0.4.0 - Production
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;
but I have this error :
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.
even I set all these variables:
SET LONG 2000000
SET pagesize 50000
set longchunk 1000
set linesize 1000
