oracle如何把blob转换成char,oracle blob字段转为varchar2

注意 return clob 类型。

varchar2  在schema级和在PL/SQL代码级长度限制不同

4000 ，32767

所以反回用 clob类型。

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;

同类函数：

create or replace function get_blobvar(tempblob in Blob) return varchar2 is

ret_buf       varchar2(32767);

l_blob        BLOB;

l_blob_length NUMBER;

l_amount      BINARY_INTEGER := 10000; -- must be <= ~32765.

l_offset      INTEGER := 1;

l_buffer      RAW(20000);

l_text_buffer VARCHAR2(32767);

l_break_at    PLS_INTEGER := 250;

BEGIN

l_blob := tempblob;

IF (l_blob IS NULL ) THEN

return '';

end if;

l_blob_length := DBMS_LOB.GETLENGTH(l_blob);

ret_buf:='';

--return to_char(l_blob_length);

FOR i IN 1 .. CEIL(l_blob_length / l_amount) LOOP

DBMS_LOB.READ(l_blob, l_amount, l_offset, l_buffer);

l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);

return l_text_buffer;

FOR j IN 1 .. CEIL(LENGTH(l_text_buffer) / l_break_at) LOOP

--ret_buf := SUBSTR(CONVERT(l_text_buffer, 'ZHS16GBK', 'AL16UTF16'),

ret_buf := ret_buf || SUBSTR(l_text_buffer,

(((j - 1) * l_break_at) + 1),

LEAST(LENGTH(l_text_buffer) - ((j - 1) * l_break_at),

l_break_at));

END LOOP;

l_offset := l_offset + l_amount;

END LOOP;

return ret_buf;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM, 1, 247));

RAISE;

END get_blobvar;

转 clob

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;