oracle中varchar2字段存入blob字段及blob转成varchar2
CREATE OR REPLACE FUNCTION C2B (b IN CLOB default empty_clob()) RETURN BLOB -- typecasts BLOB to CLOB (binary conversion) IS res BLOB; b_len number := dbms_lob.getlength(b) ; dest_offset1 NUMBER := 1; src_offset1 NUMBER := 1; amount_c INTEGER := DBMS_LOB.lobmaxsize; blob_csid NUMBER := DBMS_LOB.default_csid; lang_ctx INTEGER := DBMS_LOB.default_lang_ctx; warning INTEGER; BEGIN if b_len > 0 then DBMS_LOB.createtemporary (res, TRUE); DBMS_LOB.OPEN (res, DBMS_LOB.lob_readwrite); DBMS_LOB.convertToBlob (res, b, amount_c, dest_offset1, src_offset1, blob_csid, lang_ctx, warning ); else select empty_blob() into res from dual ; end if ; RETURN res; -- res is OPEN here END C2B;
1,首先利用to_clob函数把varchar2字段转成 clob字段。
2 利用上面函数将clob转成blob。
说白了就是 c2b(to_clob(varchar2字段))
create or replace Function Blob_To_Varchar (Blob_In In Blob) Return clob Is V_Varchar Varchar2(32767); V_Varchar1 Varchar2(32767); V_Start Pls_Integer := 1; V_Buffer Pls_Integer := 4000; 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)); V_Varchar1 := V_Varchar1 || V_Varchar; V_Start := V_Start + V_Buffer; End Loop; Return V_Varchar1; End Blob_To_Varchar;
1.首先执行上面的函数,返回的是个clob
2将clob转成varchar2
说白了 就是 to_char(Blob_To_Varchar(字段))
创建二个函数 然后就可以用了,如果说实现 数据库里面大字段的批量替换还是比较方便
update DR_RPT_REPORT_DATASOURCE
set data_sql =
c2b(to_clob((select replace(Blob_To_Varchar(data_sql),'XXX','XXX') from DR_RPT_REPORT_DATASOURCE where data_id
='XXXX' ))) where data_id = '' ;
原文在这:http://blog.csdn.net/wbo112/article/details/9041575