昨天遇到一个问题,ORA-22835 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小,去找问题时候,发现是sql查询语句的to_char方法将clob类型转换成varchar类型出了问题,oracle中varchar最大的长度是4000。把clob大文本类型直接转换成varcahr类型时:在clob的长度小于4000的时候,没有超过varchar的最大值,不会出问题,一旦超过4000就会报“ORA-22835 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小”。
查询了相关资料后发现有两种解决方法:
①java中解决:利用流将数据读取到char[]中,然后再将char[].toString():
public String clobToString(){
Reader inReader;
try {
inReader = clob.getCharacterStream();
BufferedReader br = new BufferedReader(inReader);
String s = br.readLine();
StringBuffer sb = new StringBuffer();
while (s != null) {
sb.append(s);
s = br.readLine();
}
return sb.toString();
} catch (SQLException e) {
e.printStackTrace();
}catch(IOException e){
e.printStackTrace();
}
}
②sql中解决:将bold转换成clob,再将clob转换成varchar(注:从http://blog.csdn.net/wbo112/article/details/9041575中摘取):
oracle中varchar2字段存入blob字段及blob转成varchar2
CREATE OR REPLACE FUNCTION C2B (b IN CLOB default empty_clob()) RETURN BLOB
-- typecasts BLOB to CLOB (binary conversion)
IS
res BLOB;
b_len number := dbms_lob.getlength(b) ;
dest_offset1 NUMBER := 1;
src_offset1 NUMBER := 1;
amount_c INTEGER := DBMS_LOB.lobmaxsize;
blob_csid NUMBER := DBMS_LOB.default_csid;
lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
warning INTEGER;
BEGIN
if b_len > 0 then
DBMS_LOB.createtemporary (res, TRUE);
DBMS_LOB.OPEN (res, DBMS_LOB.lob_readwrite);
DBMS_LOB.convertToBlob (res,
b,
amount_c,
dest_offset1,
src_offset1,
blob_csid,
lang_ctx,
warning
);
else
select empty_blob() into res from dual ;
end if ;
RETURN res; -- res is OPEN here
END C2B;
1,首先利用to_clob函数把varchar2字段转成 clob字段。
2 利用上面函数将clob转成blob。
说白了就是 c2b(to_clob(varchar2字段))
create or replace Function Blob_To_Varchar (Blob_In In Blob) Return clob
Is
V_Varchar Varchar2(32767);
V_Varchar1 Varchar2(32767);
V_Start Pls_Integer := 1;
V_Buffer Pls_Integer := 4000;
Begin
If Dbms_Lob.Getlength(Blob_In) Is Null Then
Return '';
End If;
V_Varchar1 := '';
--return to_char(Ceil(Dbms_Lob.Getlength(Blob_In) / V_Buffer));
For I In 1..Ceil(Dbms_Lob.Getlength(Blob_In) / V_Buffer) Loop
--当转换出来的字符串乱码时,可尝试用注释掉的函数
--V_Varchar := Utl_Raw.Cast_To_Varchar2(Utl_Raw.Convert(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'));
V_Varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start));
V_Varchar1 := V_Varchar1 || V_Varchar;
V_Start := V_Start + V_Buffer;
End Loop;
Return V_Varchar1;
End Blob_To_Varchar;
1.首先执行上面的函数,返回的是个clob
2将clob转成varchar2
说白了 就是 to_char(Blob_To_Varchar(字段))
创建二个函数 然后就可以用了,如果说实现 数据库里面大字段的批量替换还是比较方便
update DR_RPT_REPORT_DATASOURCE
set data_sql =
c2b(to_clob((select replace(Blob_To_Varchar(data_sql),'XXX','XXX') from DR_RPT_REPORT_DATASOURCE where data_id
='XXXX' ))) where data_id = '' ;