mysql blob转varchar2_Oracle Blob转成字符串

Oracle数据库中的Blob字段转成字符串的函数：

1、Utl_Raw.Cast_To_Varchar2(blob_var)只支持Blob长度小于2000的字段：

--Mysql

Select Count(*) From Score_News_Online A Where A.Onlineflag=1 And Trim(Unhex(Hex(A.Onetitle)))=Trim(Unhex('D5AEC8A8'))

And A.Newstype='news_sc_newstype_yxdt' And (A.Asstitle Between '10000000' And '50000000')

--Oracle

Select Count(*) From Bp_Winner_Ticketphoto A Where Convert(Utl_Raw.Cast_To_Varchar2(A.Photodata), 'utf8', 'zhs16gbk')='债权'

And A.Newstype='news_hub_newstype_yxdt' And (A.Asstitle Between '10000000' And '50000000')

2、Blob_To_Varchar 自定义函数支持Blob长度大于2000的字段：

--Only for Oracle 注意字段数据类型转换

Create Or Replace Function Blob_To_Varchar (Blob_In In Blob) Return Varchar2

Is

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 NETHERLANDS.UTF8'));

V_Varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start));

V_Start := V_Start + V_Buffer;

End Loop;

Return V_Varchar;

End Blob_To_Varchar;