blob转换为clob数据的函数
CREATE OR REPLACE FUNCTION b2c( b IN BLOB )
RETURN CLOB
-- typecasts BLOB to CLOB (binary conversion)
IS
pos PLS_INTEGER := 1;
buffer VARCHAR2( 32767 );
res CLOB;
lob_len PLS_INTEGER := DBMS_LOB.getLength( b );
BEGIN
DBMS_LOB.createTemporary( res, TRUE );
DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
-- if lob_len > 0 then
LOOP
buffer := UTL_RAW.cast_to_varchar2( DBMS_LOB.SUBSTR( b, 16000, pos ) );
IF LENGTH( buffer ) > 0 THEN
DBMS_LOB.writeAppend( res, LENGTH( buffer ), buffer );
END IF;
pos := pos + 16000;
EXIT WHEN pos > lob_len;
END LOOP;
-- else
-- select empty_clob() into res from dual ;
--end if ;
RETURN res; -- res is OPEN here.
end b2c ;
CREATE TABLE TEST_BLOB
(
TID NUMBER,
FNAME VARCHAR2(100),
BTEXT BLOB
);
create table xxx
as
select tid,fname,b2c(btext) text from test_blob;
declare
locator_var clob;
vid integer;
amount_var integer;
offset_var integer;
output_var varchar2(500);
begin
amount_var :=80;
offset_var :=1;
select text into locator_var from xxx
where tid=3;
dbms_lob.read(locator_var,amount_var,offset_var,output_var);
dbms_output.put_line(output_var);
end;
邢唷??
转换成功,但是读出的数据还是乱码??请问这种转换需要特定的字符集吗?我的字符集是zhs16gbk
不过新插入的数据可以正常读出了