oracle将字符串转化为blob,oracle String类型转换成blob类型插入

CERT_1以前是varchar(3000),现在的类型是blob类型，做数据库迁移时，使用导入工具不能直接导入，所以使用SQL，下面是SQL

declare

directions      BLOB;

amount          BINARY_INTEGER;

offset          INTEGER;

first_direction VARCHAR2(3000);

more_directions VARCHAR2(3000);

CURSOR c_TB1 Is

(

Select CERT_ID as ids, CERT_1 as blb From uaapra.tb_user_cert tb1 where CERT_1 is not null);

v_ids integer ;

begin

OPEN c_TB1;

LOOP

fetch c_TB1

into v_ids, first_direction;

update tb_user_cert set cert_1  = empty_blob() where cert_id  = v_ids; --更新和新增一样要将BLOB字段设置为EMPTY_BLOB()

select cert_1  into directions from tb_user_cert where cert_id  = v_ids for update; --一定要用for update锁住记录，否则

--DBMS_LOB.OPEN会出错

DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);

amount := LENGTHB(first_direction); --number of characters to write

--有中文必须用LENGTHB

offset := 1; --begin writing to the first character of the CLOB

DBMS_LOB.WRITE(directions,

amount,

offset,

UTL_RAW.cast_to_raw(first_direction));

--UTL_RAW.cast_to_raw函数将字符串转换成二进制数

DBMS_LOB.CLOSE(directions);

EXIT WHEN c_TB1%NOTFOUND;

END LOOP;

COMMIT;

CLOSE c_TB1;

end;