blob转clob和varchar2

CREATE OR REPLACE FUNCTION blob_to_clob2 (blob_in IN BLOB)
RETURN CLOB
AS
	v_clob    CLOB;
	v_varchar VARCHAR2(32767);
	v_start	 PLS_INTEGER := 1;
	v_buffer  PLS_INTEGER := 32767;
BEGIN
	DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
 if DBMS_LOB.GETLENGTH(blob_in) is null then
        return empty_clob();
 end if;
	FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
	LOOP

	   v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

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

		v_start := v_start + v_buffer;
	END LOOP;

   RETURN v_clob;

END blob_to_clob2;

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;