查询sql如下:
select (select to_char(wm_concat(a.description)) from project_report_detail a where project_report_id = t.id) as description2 , t.* from project_report t where 1=1 order by t.dept_id
然后由于project_report_detail 表的description字段是多条数据拼接成一个字段,拼接出来的结果字符串超过了sql的varchar2类型4000字符,所以报了此错:
缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 4763, 最大: 4000)
百度了一下找到了靠谱的解决办法,借鉴博客:http://www.linuxboy.net/linuxjc/137415.html
我的解决办法如下:查询的sql不再用to_char转成字符串,直接查出拼接字段的clob类型
select (select wm_concat(a.description) from project_report_detail a where project_report_id = t.id) as description2 , t.* from project_report t where 1=1 order by t.dept_id
然后在java中,用上面博客的方法处理成String类型:
private String ClobtoString(Clob clob){ String reString = ""; Reader is = null; try { is = clob.getCharacterStream(); } catch (Exception e) { e.printStackTrace(); } // 得到流 BufferedReader br = new BufferedReader(is); String s = null; try { s = br.readLine(); } catch (Exception e) { e.printStackTrace(); } StringBuffer sb = new StringBuffer(); while (s != null) { // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING sb.append(s); try { s = br.readLine(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } reString = sb.toString(); return reString; }
成功解决,亲测有效
Oracle查数据并使用wm_concat函数拼接字段报错:缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 4763, 最大: 4000)