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;