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;