做完了一个项目 在导出excel的时候 报错
错误是 ORA-06502:PL/SQL 数字或值错误:字符串缓冲区太小 和 ORA-06521:在“WMSYS.WM_CONCAT_IMPL”,LINE 30
sql语句是
SELECT DISTINCT FW.FWBH,FW.FWLX,FW.MGS,
(SELECT MIN(SH_CLGCJL.CZSJ)FROM SH_CLGCJL, SH_BDSHFU
WHERE SH_CLGCJL.FWBH = SH_BDSHFU.PK1 AND SH_BDSHFU.PK1 = '1761A7C2E5855DF1CADDC3D445BC999D'
GROUP BY SH_CLGCJL.FWBH) AS CZSJ,
(SELECT MAX(SH_CLGCJL.FKSJ)
FROM SH_CLGCJL, SH_BDSHFU
WHERE SH_CLGCJL.FWBH = SH_BDSHFU.PK1 AND SH_BDSHFU.PK1 = '1761A7C2E5855DF1CADDC3D445BC999D'
GROUP BY SH_CLGCJL.FWBH) AS FKSJ,
FW.LXRBH,FW.LXFS,
(SELECT S3.CNNAME AS FW_SLR FROM SH_CLGCJL JL LEFT JOIN SH_USER S3 ON (S3.ENNAME = JL.FW_SLR)
WHERE FWBH = '1761A7C2E5855DF1CADDC3D445BC999D' AND ROWNUM = 1) AS FW_SLR,
FW.NR,FW.BYZD2,
S2.CNNAME AS BYZD3,
(SELECT REPLACE(WMSYS.WM_CONCAT(M.JD), ',', '||') RESULT
FROM (SELECT REPLACE(WMSYS.WM_CONCAT(SH_CLGCJL.FKSJ || ' ' ||S5.CNNAME || ' ' ||SH_CLGCJL.BYZD1),',',
' || ') JD FROM SH_CLGCJL LEFT JOIN S_USER S5 ON (S5.ENNAME =SH_CLGCJL.FW_SLR)
WHERE SH_CLGCJL.FWBH ='1761A7C2E5855DF1CADDC3D445BC999D' AND S5.ENNAME = SH_CLGCJL.FW_SLR
GROUP BY SH_CLGCJL.FKSJ) M) AS BYZD1,
FW.FKYJ,
FW.BZ,
FW.PK1
FROM SH_BDSHFU FW
LEFT JOIN S_DIC S2 ON (S2.TYPE1 = '完成情况' AND S2.ENNAME = FW.BYZD3),
SH_CLGCJL JL
WHERE FW.PK1 = JL.FWBH
AND FW.PK1 = '1761A7C2E5855DF1CADDC3D445BC999D'
AND FW.BYZD3 = '01'
这个怎么解决那个缓冲的大小问题呢 网上找了好多都不能实现 谁能帮忙解决一下谢了
错误是 ORA-06502:PL/SQL 数字或值错误:字符串缓冲区太小 和 ORA-06521:在“WMSYS.WM_CONCAT_IMPL”,LINE 30
sql语句是
SELECT DISTINCT FW.FWBH,FW.FWLX,FW.MGS,
(SELECT MIN(SH_CLGCJL.CZSJ)FROM SH_CLGCJL, SH_BDSHFU
WHERE SH_CLGCJL.FWBH = SH_BDSHFU.PK1 AND SH_BDSHFU.PK1 = '1761A7C2E5855DF1CADDC3D445BC999D'
GROUP BY SH_CLGCJL.FWBH) AS CZSJ,
(SELECT MAX(SH_CLGCJL.FKSJ)
FROM SH_CLGCJL, SH_BDSHFU
WHERE SH_CLGCJL.FWBH = SH_BDSHFU.PK1 AND SH_BDSHFU.PK1 = '1761A7C2E5855DF1CADDC3D445BC999D'
GROUP BY SH_CLGCJL.FWBH) AS FKSJ,
FW.LXRBH,FW.LXFS,
(SELECT S3.CNNAME AS FW_SLR FROM SH_CLGCJL JL LEFT JOIN SH_USER S3 ON (S3.ENNAME = JL.FW_SLR)
WHERE FWBH = '1761A7C2E5855DF1CADDC3D445BC999D' AND ROWNUM = 1) AS FW_SLR,
FW.NR,FW.BYZD2,
S2.CNNAME AS BYZD3,
(SELECT REPLACE(WMSYS.WM_CONCAT(M.JD), ',', '||') RESULT
FROM (SELECT REPLACE(WMSYS.WM_CONCAT(SH_CLGCJL.FKSJ || ' ' ||S5.CNNAME || ' ' ||SH_CLGCJL.BYZD1),',',
' || ') JD FROM SH_CLGCJL LEFT JOIN S_USER S5 ON (S5.ENNAME =SH_CLGCJL.FW_SLR)
WHERE SH_CLGCJL.FWBH ='1761A7C2E5855DF1CADDC3D445BC999D' AND S5.ENNAME = SH_CLGCJL.FW_SLR
GROUP BY SH_CLGCJL.FKSJ) M) AS BYZD1,
FW.FKYJ,
FW.BZ,
FW.PK1
FROM SH_BDSHFU FW
LEFT JOIN S_DIC S2 ON (S2.TYPE1 = '完成情况' AND S2.ENNAME = FW.BYZD3),
SH_CLGCJL JL
WHERE FW.PK1 = JL.FWBH
AND FW.PK1 = '1761A7C2E5855DF1CADDC3D445BC999D'
AND FW.BYZD3 = '01'
这个怎么解决那个缓冲的大小问题呢 网上找了好多都不能实现 谁能帮忙解决一下谢了
解决方案 »
- 求一个多表查询的sql语句
- oracle取不重复数据的问题
- 关于bat调用sql文件。
- oracle中用什么命令来显示数据库名列表?急~~~~~~~~~
- pl/sql的问题
- 拉链表或是type2表脏数据修复问题
- 请教:怎么样把EXCEL的数据导入ORCAL?
- 请问oracle 8 和 oracle 9i 的区别,是否能用8 的书来学 9i?
- 请教:从sql server2000导出数据到oracle8i,表名是大小写混写的,到oracle也是,所以出了问题。
- oracle9i 在redhat8下安装,安装程序的揭示信息是乱码
- ORA-04098: 触发器 'SCOTT.ORDERDELETE' 无效且未通过重新验证
- 求一个随机数生成存储过程
WMSYS.WM_CONCAT(SH_CLGCJL.FKSJ || ' ' ||S5.CNNAME || ' ' ||SH_CLGCJL.BYZD1)注意观察你的这两句里的输入内容,有点问题,当输入的字符长度超过4000时,就会出现字符串缓冲区太小。
或者自己写个函数,看看这个
http://blog.163.com/leo_wuya/blog/static/44451843201123023659183/