在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