oracle clob raw 转换,ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小...

报错信息：

Tue Jan 06 09:34:24 2015

Errors in file /ggs/oracle_log/diag/rdbms/orayy3/oyy3a/trace/oyy3a_ora_10028532.trc:

ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 9419, 最大: 4000)

Tue Jan 06 09:34:31 2015

Errors in file /ggs/oracle_log/diag/rdbms/orayy3/oyy3a/trace/oyy3a_ora_39191072.trc:

ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 12131, 最大: 4000)

Tue Jan 06 09:34:45 2015

Errors in file /ggs/oracle_log/diag/rdbms/orayy3/oyy3a/trace/oyy3a_ora_39191072.trc:

ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 5375, 最大: 4000)

Tue Jan 06 09:34:50 2015

Errors in file /ggs/oracle_log/diag/rdbms/orayy3/oyy3a/trace/oyy3a_ora_48103584.trc:

ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 4607, 最大: 4000)

Tue Jan 06 09:35:26 2015

[oracle@c4oyy3a] /arch5>more /ggs/oracle_log/diag/rdbms/orayy3/oyy3a/trace/oyy3a_ora_10028532.trc

Trace file /ggs/oracle_log/diag/rdbms/orayy3/oyy3a/trace/oyy3a_ora_10028532.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1

System name:    AIX

Node name:      c4oyy3a

Release:        1

Version:        6

Machine:        00F6FA744C00

Instance name: oyy3a

Redo thread mounted by this instance: 1

Oracle process number: 1782

Unix process pid: 10028532, image: oracle@c4oyy3a

*** 2015-01-06 09:34:24.121

*** SESSION ID:(2339.62773) 2015-01-06 09:34:24.121

*** CLIENT ID:() 2015-01-06 09:34:24.121

*** SERVICE NAME:(orayy3) 2015-01-06 09:34:24.121

*** MODULE NAME:(JDBC Thin Client) 2015-01-06 09:34:24.121

*** ACTION NAME:() 2015-01-06 09:34:24.121

ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 9419, 最大: 4000)

Dump of memory from 0x0700000BD4F54350 to 0x0700000BD4F54BA0

700000BD4F54350 77697468 20736572 76696365 7461736B [with servicetask]

700000BD4F54360 4C697374 20617320 20202873 656C6563  [List as   (selec]

700000BD4F54370 7420746F 5F636861 7228776D 5F636F6E [t to_char(wm_con]

700000BD4F54380 63617428 73742E73 65727669 63656E6F  [cat(st.serviceno]

700000BD4F54390 29292073 65727669 63656E6F 2C20746F  [)) serviceno, to]

700000BD4F543A0 5F636861 7228776D 5F636F6E 63617428  [_char(wm_concat(]

700000BD4F543B0 64696374 322E6469 63746E61 6D652929  [dict2.dictname))]

700000BD4F543C0 20736572 76696365 6E6F6E61 6D652020  [ servicenoname  ]

700000BD4F543D0 20202020 66726F6D 20646963 745F6974  [    from dict_it]

700000BD4F543E0 656D2064 69637432 2C206D6D 5F776B5F  [em dict2, mm_wk_]

700000BD4F543F0 73657276 69636574 61736B20 73742020  [servicetask st  ]

700000BD4F54400 20202077 68657265 20646963 74322E67  [   where dict2.g]

700000BD4F54410 726F7570 6964203D 2073742E 73657276  [roupid = st.serv]

700000BD4F54420 69636574 79706569 64202020 20616E64  [icetypeid    and]

700000BD4F54430 20646963 74322E64 69637469 64203D20  [ dict2.dictid = ]

700000BD4F54440 73742E73 65727669 63656E6F 20202020  [st.serviceno    ]

700000BD4F54450 616E6420 73742E72 6567696F 6E203D20  [and st.region = ]

700000BD4F54460 35333020 20202061 6E642073 742E736F  [530    and st.so]

700000BD4F54470 75726365 6F696420 3D203A31 292C2020  [urceoid = :1),  ]

700000BD4F54480 20736D6D 734C6973 74206173 20202028  [ smmsList as   (]

700000BD4F54490 73656C65 63742074 6F5F6368 61722877  [select to_char(w]

700000BD4F544A0 6D5F636F 6E636174 28736D6D 732E7365  [m_concat(smms.se]

700000BD4F544B0 72766E75 6D626572 29292073 6572766E  [rvnumber)) servn]

700000BD4F544C0 756D6265 722C2074 6F5F6368 61722877  [umber, to_char(w]

700000BD4F544D0 6D5F636F 6E636174 28736D6D 732E7365  [m_concat(smms.se]

700000BD4F544E0 72766E61 6D652929 20736572 766E616D  [rvname)) servnam]

700000BD4F544F0 65202020 20202066 726F6D20 6D6D5F77  [e      from mm_w]

700000BD4F54500 6B5F736D 6D732073 6D6D7320 20202020  [k_smms smms     ]

700000BD4F54510 77686572 6520736D 6D732E72 6567696F  [where smms.regio]

700000BD4F54520 6E203D20 35333020 20202061 6E642073  [n = 530    and s]

700000BD4F54530 6D6D732E 62617463 68696420 3D203A32  [mms.batchid = :2]

700000BD4F54540 29202020 20202020 20205345 4C454354  [)         SELECT]

700000BD4F54550 20534552 564C4F47 2E4F4944 204F4944  [ SERVLOG.OID OID]

700000BD4F54560 2C202020 20202020 20205345 52564C4F  [,         SERVLO]

700000BD4F54570 472E5245 47494F4E 2C202020 20202020  [G.REGION,       ]

700000BD4F54580 20205345 52564C4F 472E5345 52564943  [  SERVLOG.SERVIC]

700000BD4F54590 45545950 4549442C 20202020 20202020  [ETYPEID,        ]

700000BD4F545A0 20202020 20202020 20202020 20202020  [                ]

从trc文件里可以看出，当时影响ORA错误的语句如下：

with servicetaskList as   (select to_char(wm_concat(st.serviceno)) serviceno, to_char(wm_concat(dict2.dictname)) servicenoname      from dict_item dict2, mm_wk_servicetask st     where dict2.groupid = st.servicetypeid    and dict2.dictid = st.serviceno    and st.region = 530    and st.sourceoid = :1),   smmsList as   (select to_char(wm_concat(smms.servnumber)) servnumber, to_char(wm_concat(smms.servname)) servname      from mm_wk_smms smms     where smms.region = 530    and smms.batchid = :2)         SELECT SERVLOG.OID OID,         SERVLOG.REGION,         SERVLOG.SERVICETYPEID,                                    (select st.serviceno from servicetaskList st) serviceno,           (select st.servicenoname from servicetaskList st) servicenoname,           (select smms.servnumber from smmsList smms) LINKPHONE,           (select smms.servname from smmsList smms) NAME,           servlog.CHIEFCUSTMGR,           servlog.SENDSMS,                              SERVLOG.CUSTNO CUSTNO,         SERVLOG.CUSTNAME CUSTNAME,         SERVLOG.SERVICEMETHOD,         TO_CHAR(SERVLOG.SERVICEDATE, 'YYYY-MM-DD HH24:MI:SS') SERVICEDATE,         SERVLOG.SERVICECONTENT SERVICECONTENT,         SERVLOG.SOURCEOID SOURCEOID,         SERVLOG.CUSTTYPE CUSTTYPE,         SERVLOG.STATUS,         SERVLOG.CUSTOMERTYPE,         (SELECT T.RESNAME            FROM T_PUB_SRVRESOURCE T           WHERE T.OID = to_number(SERVLOG.RESID)) RESID,         SERVLOG.RESNUM,         SERVLOG.TOUCHSUCCEED,         SERVLOG.CONCERTDEGREE,         SERVLOG.SATISFYDEGREE,         SERVLOG.EVALUATESTAFFNO,         (SELECT OPER.OPERNAME || '[' || SERVLOG.EVALUATESTAFFNO || ']'            FROM OPERATOR OPER           WHERE OPER.OPERID = SERVLOG.EVALUATESTAFFNO) EVALUATESTAFFNAME,                      SERVLOG.MEMBERTYPE,                 (      SELECT SIGN.LONGITUDE FROM MM_MK_CUSTMGR_SIGN SIGN WHERE SIGN.REGION = 530 AND SIGN.OID=SERVLOG.OID      ) LONGITUDE,      (      SELECT SIGN.LATITUDE FROM MM_MK_CUSTMGR_SIGN SIGN WHERE SIGN.REGION = 530 AND SIGN.OID=SERVLOG.OID      ) LATITUDE    FROM MM_WK_COMMONWORKLOG SERVLOG   WHERE SERVLOG.OID = :3

问题分析：

该语句多处使用wm_concat()这个函数转换，这个函数的功能是列转行以逗号分隔；

正因如此，假如查询的结果很多，to_char(vm_concat()) 超过4000时会报错，因为varchar2最大字节4000；

解决方法：

1.减少查询的返回结果集；

2.可能通过to_char(substr(vm_concat()))来截取字符串长度