blob转varchar

```sql
CREATE OR REPLACE FUNCTION BLOB_TO_VARCHAR_SPP(BLOB_IN IN BLOB)
  RETURN VARCHAR2 IS
  V_VARCHAR VARCHAR2(32767);
  --V_VARCHAR1 VARCHAR2(32766);
  V_VARCHAR1 NVARCHAR2(2000);
  V_START    PLS_INTEGER := 1;
  V_BUFFER   PLS_INTEGER := 32767;
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));
    if (lengthb(V_VARCHAR1 || V_VARCHAR) >= 3999) then
      RETURN V_VARCHAR1;
    end if;
    V_VARCHAR1 := V_VARCHAR1 || V_VARCHAR;

V_START := V_START + V_BUFFER;
  END LOOP;

RETURN V_VARCHAR1;

END BLOB_TO_VARCHAR_SPP;

```