带LONG类型的表数据迁移

今天迁移表的时候，发现有几个表通过INSERT INTO..SELECT ..的方式迁移出错了。原来是这几个表中有LONG字段。有LONG字段的表不能通过INSERT INTO..SELECT ..或CTAS的方法来迁移数据。很蛋疼！

Google之后，发现可以用CURSOR一行一行地插入到目标表中。

以下是针对某个表的迁移脚本（没办法通过，只能一个表写一次。遇到批量的情况就悲剧了！）

--带LONG类型的表数据迁移
DECLARE
  CURSOR my_cursor IS
    SELECT road_id,
           version,
           create_user,
           date_created,
           last_updated,
           record_status,
           remark,
           road_name,
           region,
           road_type,
           direction,
           monitor,
           road_length,
           road_code,
           show_level,
           from_longitude,
           from_latitude,
           to_longitude,
           to_latitude,
           parent_id
    FROM   app.bs_road@db_remote;
l_data my_cursor%ROWTYPE;
  v_sqlstr VARCHAR2(32726);
BEGIN
  OPEN my_cursor;
  LOOP
    FETCH my_cursor
      INTO l_data;
    EXIT WHEN my_cursor%NOTFOUND;
    EXECUTE IMMEDIATE 'INSERT INTO TEST.BS_ROAD(ROAD_ID,VERSION,CREATE_USER,DATE_CREATED,LAST_UPDATED,RECORD_STATUS,REMARK,ROAD_NAME,REGION,ROAD_TYPE,DIRECTION,MONITOR,ROAD_LENGTH,ROAD_CODE,SHOW_LEVEL,FROM_LONGITUDE,FROM_LATITUDE,TO_LONGITUDE,TO_LATITUDE,PARENT_ID) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20)'
      USING l_data.road_id, l_data.version, l_data.create_user, l_data.date_created, l_data.last_updated, l_data.record_status, l_data.remark, l_data.road_name, l_data.region, l_data.road_type, l_data.direction, l_data.monitor, l_data.road_length, l_data.road_code, l_data.show_level, l_data.from_longitude, l_data.from_latitude, l_data.to_longitude, l_data.to_latitude, l_data.parent_id;
    COMMIT;
  END LOOP;
  CLOSE my_cursor;
END;
/

参考http://itpremier.itpub.net/post/5875/516302

另外呢，带LONG的表，还可以通过exp/imp来迁移，这里就不啰嗦了。