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

--如果乱码了就需要调整字符集了 --select userenv('language') from dual 查看数据库编码

 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(utl_raw.convert(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'));

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

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

--如果乱码了，就需要调整字符集了 select userenv('language') from dual --查看数据库编码

 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(utl_raw.convert(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK','SIMPLIFIED CHINESE_CHINA.ZHS16GBK'));

 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 Clob_To_Blob (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;

END Clob_To_Blob;