SYS_CONNECT_BY_PATH с CLOB

У меня есть ошибка ORA-01489: результат конкатенации строк слишком длинный при выполнении этого запроса в Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, PL / SQL Release 11.2.0.4. 0 - Производство, CORE 11.2.0.4.0 Производство, TNS для Linux: Версия 11.2.0.4.0 - Производство, NLSRTL Версия 11.2.0.4.0 - Производство:

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;

Проблема с этим запросом связана с использованием оператора CONCAT (||). Оператор Concat возвращает char1, объединенный с char2. Возвращаемая строка имеет тот же набор символов, что и char1. Итак, здесь оператор concat пытается вернуть varchar2, который имеет ограничение в 4000 символов и превышает его. Эта проблема также может возникнуть, когда мы пытаемся СВЯЗАТЬ VARCHAR2 с CLOB. Итак, здесь я хочу просто преобразовать его первую строку в CLOB и избежать этой ошибки. После преобразования первой строки в CLOB оператор CONCAT вернет строку типа CLOB.

Поэтому я добавляю TO_CLOB для преобразования типов, но затем возникает следующая ошибка:

ORA-00932: несогласованные типы данных: ожидалось - получено 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;

Я также попытался использовать определенную здесь иерархию пакетов, но затем я получил ORA- 00932: несовместимые типы данных: ожидалось - получил 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;

Затем я тоже попробовал с sys.stragg, но получил ORA-00978: вложенная групповая функция без 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;

sql oracle oracle11g string-aggregation
person Nunyet de Can Calçada    schedule 17.05.2016    source источник
comment
SYS_CONNECT_BY_PATH принимает в качестве входных данных тип данных char / varchar, а CLOB - нет. Загляните сюда community.oracle.com/thread/965324?start=0&tstart = 0, так как это похоже на ту же проблему, что и у вас. Основная идея состоит в том, чтобы добавить еще один уровень упаковки, где вы можете объединить короткие строки в clob.   -  person micklesh    schedule 17.05.2016
comment
И еще одна идея использовать STRAGG вместо sys_connect_by_path: еще две ссылки asktom.oracle.com/pls/apex/ asktom.oracle.com/pls/asktom/   -  person micklesh    schedule 17.05.2016
comment
Скорее всего, вам нужно будет более четко просмотреть свои представления, используя факторинг подзапроса. Это позволит вам по-другому структурировать данные и ускорить выполнение запросов за счет материализации частей данных. Рассмотрите возможность использования предложения WITH и примените рекурсивный рефакторинг подзапросов для достижения цели иерархического структурирования набора результатов. Это мощный метод написания ваших запросов (вложенные запросы WITH).   -  person Pete Mahon    schedule 19.06.2016
comment
Кроме того, приведенное выше предложение оставить элементы clob до конца также правильно, иначе вы напрасно заполняете память. Постройте иерархию с ключевыми элементами, вводя первичный ключ, если это необходимо в вашем представлении, чтобы вы могли вернуться и ссылаться на слитки из отсортированных данных. Надеюсь это поможет   -  person Pete Mahon    schedule 19.06.2016

Ответы (2)


arrow_upward
2
arrow_downward

Вы можете построить иерархию CLOB путь с помощью синтаксиса факторинга подзапроса Это может работать очень медленно. Рассмотрите возможность наличия двух столбцов пути - одного для varchar2 результата и одного для CLOB. Стройте varchar2, пока размер не позволит, и оставьте NULL в CLOB пути, и переключитесь на CLOB, когда емкость varchar2 закончится. Но это другой вопрос.

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
person B Samedi    schedule 01.04.2017

arrow_upward
0
arrow_downward

Возможно, вы нашли эту страницу, потому что пытаетесь агрегировать строки длиной более 4000 символов и запомнили разные техники.

Если это так, я создал небольшой пример, основанный на ответе @B Samedi, чтобы помочь вам, когда вы не можете использовать определяемые пользователем агрегаты

with dummy_text as (
select 'teststring ' || rownum str from dual connect by rownum < 2
)

, indexed_strings as (
select str, row_number() over (order by 'x') rn, ',' separator from dummy_text
)

, hier (str, lvl) as (
select to_clob(i.str), rn from indexed_strings i where rn = (select max(rn) from indexed_strings)
union all
select concat(to_clob(concat(i.str, i.separator)), h.str), h.lvl - 1 from indexed_strings i join hier h on h.lvl - 1 = i.rn
)

select str from hier where lvl = 1
person casenonsensitive    schedule 11.07.2020