在Oracle数据库中有一张表为questionbasetbl。里面有一个字段answer，类型为blob。现在需求变了，要将字段answer字段的类型改成varchar2。
整体上的思路：
1、将字段answer改名为answer2，
ALTER TABLE questionbasetbl RENAME COLUMN answer TO answer2
2、创建一个类型为varchar2的answer字段
alter table questionbasetbl add answer varchar(4000);
3、创建一个函数将类型为BLOB的数据传输到varchar2的函数
create or replace FUNCTION blob_to_varchar (blob_in IN BLOB)
RETURN VARCHAR2
IS
v_varchar VARCHAR2(2000);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 2000;
BEGIN
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)));
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),'AMERICAN_THE NETHERLANDS.UTF8', 'AMERICAN_THE NETHERLANDS.UTF8'));
v_start := v_start + v_buffer;
END LOOP;
RETURN v_varchar;
end blob_to_varchar;
4、字段answer2的数据传输到字段answer
UPDATE questionbasetbl SET ANSWER=blob_to_varchar(ANSWER2);
5、删除字段answer2
alter table questionbasetbl drop column answer2