oracle tovarchar2_oracle中varchar2字段存入blob字段及blob转成varchar2

CREATE OR REPLACE FUNCTION C2B (b IN CLOB defaultempty_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;

BEGINif b_len > 0then

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 fromdual ;

endif;

RETURN res;-- res isOPEN 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