sql oracle-database oracle11g string-aggregation

SYS_CONNECT_BY_PATH with CLOB

I have an ORA-01489: result of string concatenation is too long error executing this query on 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:

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;

The problem with this query is with the use of CONCAT operator (||). Concat operator returns char1 concatenated with char2. The string returned is in the same character set as char1. So here concat operator is trying to return varchar2, which has limit of 4000 characters and getting exceeded. This problem may also come when we try to CONCAT a VARCHAR2 with CLOB. So here I want simply convert its first string to CLOB and avoid this error. After converting first string to CLOB, CONCAT operator will return string of CLOB type

So I add the TO_CLOB to convert the types but then I have the next error:

ORA-00932: inconsistent datatypes: expected - got CLOB

  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,
                        TO_CLOB(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;

I also tried to use the package Hierarchy defined Here but then I got a ORA-00932: inconsistent datatypes: expected - got CLOB https://community.oracle.com/thread/965324?start=0&tstart=0

SELECT "USER_PRIMARY_UNIT","LOGIN","FIRST_NAME","LAST_NAME","UNIT_ROLE"
FROM (
SELECT user_primary_unit,login, first_name,  last_name,
       LTRIM(MAX(hierarchy.branch(level,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,
                        TO_CLOB(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;

Then I tried as well with sys.stragg , but i got a ORA-00978: nested group function without GROUP BY

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,
                        sys.stragg(sys.stragg(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;

Solution

You can build hierarchy CLOB path with subquery factoring syntax This may run really slow. Consider having two path columns - one for varchar2 result and one for CLOB. Build varchar2 till size allows, and keep NULL in CLOB path, and switch to CLOB when out of varchar2 capacity. This is a different question though.

with
base as (
select
    level as id,
    case when level > 1 then level - 1 end as parent_id,
    dbms_random.string('X', 2000) as val
from dual
connect by level <= 50
),
hier(id, parent_id, val, path) as (
    select
        b.id,
        b.parent_id,
        b.val,
        to_clob(concat('/', b.val)) as path
    from base b
    where b.parent_id is null
    union all
    select
        b.id,
        b.parent_id,
        b.val,
        concat(h.path, to_clob(' / '||b.val) )
    from base b
        join hier h on h.id = b.parent_id

)
select rownum, length(h.path)
from hier h;

ROWNUM  LENGTH(H.PATH)
1   2001
2   4004
3   6007
4   8010
5   10013
6   12016
7   14019
8   16022
9   18025
10  20028
11  22031
12  24034
13  26037
14  28040
15  30043
16  32046
17  34049
18  36052
19  38055
20  40058
21  42061
22  44064
23  46067
24  48070
25  50073
26  52076
27  54079
28  56082
29  58085
30  60088
31  62091
32  64094
33  66097
34  68100
35  70103
36  72106
37  74109
38  76112
39  78115
40  80118
41  82121
42  84124
43  86127
44  88130
45  90133
46  92136
47  94139
48  96142
49  98145
50  100148