1 Utl_Raw.Cast_To_Varchar2(blob): blob字符数小于2000
select Utl_Raw.Cast_To_Varchar2(OBJVALUE),attributeinstance.* from attributeinstance
2. 长度大于2000,再用Utl_Raw.Cast_To_Varchar2报错,ORA-22835:缓冲区对应Clob到char转换或者BLOB到RAW转换而言太小。
自定义函数
Create Or Replace Function Blob_To_Varchar (Blob_In In Blob) Return Varchar2
AS
V_Varchar Varchar2(4000);
V_Start Pls_Integer := 1;
V_Buffer Pls_Integer := 4000;
Begin
If Dbms_Lob.Getlength(Blob_In) Is Null Then
Return '';
End If;
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