Oracle select BLOB column as CHAR

Orale 查詢BLOB欄位為字串的方式如下。

UTL_RAW.CAST_TO_VARCHAR2()function 可把BLOB轉為VARCHAR2字串

SELECT UTL_RAW.CAST_TO_VARCHAR2(BLOB_COL) FROM MY_TABLE;

UTL_ENCODE.BASE64_ENCODE()function 可吧BLOB轉為Base64 Encode的RAW字串

SELECT UTL_ENCODE.BASE64_ENCODE(BLOB_COL) FROM MY_TABLE;

但轉出的字串不能超過2000，否則會發生ORA-22835錯誤如下。

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 48443, maximum: 2000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*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.

可先用DBMS_LOB.GETLENGTH() function確認長度。

SELECT DBMS_LOB.GETLENGTH(BLOB_COL) FROM MYTABLE;