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位元組,如果轉換過來超過這個就會出錯。