oracle 字符串处理函数

ora-22835:Buffer to small for clob to char or Blob to raw conversion(actual string,maxinum string)

 

cause:an attempt was made to convert CLOB to CHAR or BLOB to RAW, where the LOB size was bigger than the buffer limit for CHAR and RAW types. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes.

 

action:Do one of the following:

        1.Make the lob smaller before performing the conversion, for example, by using SUBSTR on CLOB

        2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

 

遇到这样的情况，一般使用to_char(substr(xxx,1,length)) 进行转换，直接使用to_char进行转换就会出报ora-22835错误！

length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
在使用length()查询一个字符串长度的时候，统计出来的也是字符串的个数，而非字节数。

 

在oracle 里面varchar2,char 可以存储的长度为8000字节,如果转换过来超过这个就会出错。