当前位置: 首页 > 知识库问答 >
问题:

带CLOB的SYS_CONNECT_BY_PATH

凌轶
2023-03-14

我有一个ORA-01489:在Oracle数据库11g Enterprise Edition版本11.2.0.4.0-64bit产品、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-产品上执行此查询时,字符串连接的结果太长错误:

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运算符返回与char2串联的char1。返回的字符串与char1的字符集相同。因此,这里concat操作符试图返回varchar2,它的限制为4000个字符,并且超出了这个限制。当我们尝试用CLOB连接一个VARCHAR2时,也可能会出现这个问题。所以在这里,我想简单地将它的第一个字符串转换为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;
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;
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;

共有1个答案

方宏富
2023-03-14

您可以使用子查询分解语法构建层次结构clob路径,这可能运行得很慢。考虑有两个路径列--一个用于varchar2result,一个用于clob。在大小允许时构建varchar2,并在clob路径中保持null,当varchar2容量不足时切换到clob。不过,这是一个不同的问题。

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
 类似资料:
  • 下面的示例将演示如何在Spring JDBC的帮助下使用Update Query更新CLOB。 我们将更新学生表中的可用记录。 学生表 CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, DESCRIPTION LONGTE

  • 下面的示例将演示如何在Spring JDBC的帮助下使用Update Query更新CLOB。 我们将更新学生表中的可用记录。 学生表 CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, DESCRIPTION LONGTE

  • 问题内容: 我试图将一个超过4000个字符的值写入Oracle Clob字段。这似乎是一个普遍的问题,但是似乎没有解决方案可以奏效。所以我从这里祈祷寻求帮助。 故障和肮脏的信息:使用Oracle 9.2.0.8.0 Hibernate3实现带有注释 Tomcat的pojo Tomcat 6.0.16 Oracle 10.2.x驱动程序 C3P0连接池提供程序 在我的persistence.xml中

  • 问题内容: 我正在寻找有关BLOB和CLOB数据的真正好的解释。我正在寻找用通俗易懂的英语进行解释的伟大作品。 问题答案: BLOB(二进制大对象)存储二进制文件:图片,文本,音频文件,Word文档等。人眼无法读取的任何内容。您无法通过SQL * Plus选择它们。 CLOB(字符大对象)存储字符数据。它们通常用于存储XML文档,JSON或仅大块格式化或未格式化的文本。

  • 使用java和jmstemplate,我试图用包含CLOB字段的有效负载将消息编入oracle队列。 我的问题是你是怎么做这种事的?在谷歌上,有各种各样的建议表明我必须: 用空加载对消息进行排队 使用步骤1中创建的消息id从队列表中检索usr_data 将clob写入队列表。 约安。

  • 首先,这不是这个问题的重复。如果是的话,对不起,我不能通过阅读来解决我的问题。 非常感谢。