费了一晚上才解决的oracle数据库提示ORA-01489字符串连接的结果过长

likeyouli

首先说明，字符串肯定没有过长，最多估计都不到100，但递归查询时就是提示错误，见图，代码如下：with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order by fyfssj) as rn from tanghua  where ybxmmc like '%糖化血红蛋白%') ,
digui (shfzhm,yyxmmc,tcje,fyfssj,rn,所有日期) as (
select jichu.*,to_char(jichu.fyfssj,'yyyy-mm-dd') as 所有日期 from jichu  where rn=1
union all
select jichu.shfzhm,jichu.yyxmmc,jichu.tcje,jichu.fyfssj,jichu.rn,  (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' ||所有日期)    as 所有日期
from jichu join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select * from digui

解决方式见二楼，，高手误嘲笑，实在费了洋劲才解决的，各种百度，deepseek都白搭，偶然测试才发现解决方式，看样子我对oracle数据库的底层了解太浅了，到现在都没明白咋回事，估计to_char转换完是不是有字节大小限制啊, 算了,以后慢慢思考原理.  现心情舒畅的必须向无忧网友报告一下，有遇到同样问题的可参考解决，

likeyouli

解决代码:with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order by fyfssj) as rn from tanghua  where ybxmmc like '%糖化血红蛋白%') ,
digui (shfzhm,yyxmmc,tcje,fyfssj,rn,所有日期) as (
select jichu.*,cast(to_char(jichu.fyfssj,'yyyy-mm-dd') as varchar2(4000)) as 所有日期 from jichu  where rn=1
union all
select jichu.shfzhm,jichu.yyxmmc,jichu.tcje,jichu.fyfssj,jichu.rn,  (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' ||所有日期)    as 所有日期
from jichu join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select * from digui order by shfzhm,fyfssj

即 to_char转换为字符串后,再cast一下所有日期这列才可以, 感觉所有日期这列有点相当于变量, cast as varchar2(4000) 这样就没问题了, 这么简单的问题, 为啥百度,deepseek就解决不了呢?
是问的方式不对,还是怎么回事  ?? 老让我转to_clob,转完又报什么错误, 关键是根本就不需要转,最长的字符也不长  .
    再说句, 这两天光研究递归查询了, 总算有点明白了  

俪尚皇

满满的经验 学习了

wang1126

谢谢楼主分享

小灰兔

谢谢楼主分享

tanglf

谢谢分享经验

qqwwaa

likeyouli 发表于 2025-4-4 23:36
解决代码:with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order ...

66

旭东

字都认识
膜拜了

chinaren12

这是医院的数据库？？？楼主666啊，无忧真是藏龙卧虎啊。。

qqwwaa

不容易

yc2428

谢谢分享

lxgood

谢谢楼主分享

guong

感谢分享