create?or?replace?FUNCTION?blob_to_clob?(blob_in?IN?BLOB)?
RETURN?CLOB?
IS?
v_clob?CLOB;?
v_varchar?VARCHAR2(4000);?
v_start?PLS_INTEGER?:=?1;?
v_buffer?PLS_INTEGER?:=?4000;?
g_nls_db_char?varchar2(60);?
BEGIN?
?select?userenv('LANGUAGE')?into?g_nls_db_char?from?dual;?
?
?if?DBMS_LOB.GETLENGTH(blob_in)?is?null?then
????????return?empty_clob();??
?end?if;
?
?DBMS_OUTPUT.put_line('TEST:'?||?CEIL(DBMS_LOB.GETLENGTH(blob_in)));
?
?DBMS_LOB.CREATETEMPORARY(v_clob,?TRUE);?
?
?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'));

?DBMS_LOB.WRITEAPPEND(v_clob,?LENGTH(v_varchar),?v_varchar);?

?v_start?:=?v_start?+?v_buffer;?
?END?LOOP;?
?
?--DBMS_OUTPUT.put_line(v_varchar);
?
?RETURN?v_clob;?
?
end?blob_to_clob;?

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?
?--select?userenv('LANGUAGE')?into?g_nls_db_char?from?dual;?
?
?if?DBMS_LOB.GETLENGTH(blob_in)?is?null?then
????????return?empty_clob();??
?end?if;
?
?DBMS_OUTPUT.put_line('TEST:'?||?CEIL(DBMS_LOB.GETLENGTH(blob_in)));
?
?--DBMS_LOB.CREATETEMPORARY(v_clob,?TRUE);?
?
?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'));

?--DBMS_LOB.WRITEAPPEND(v_clob,?LENGTH(v_varchar),?v_varchar);?

?v_start?:=?v_start?+?v_buffer;?
?END?LOOP;?
?
?--DBMS_OUTPUT.put_line(v_varchar);
?
?RETURN?v_varchar;?
?
end?blob_to_varchar;?

?