2011年记　

目的：

遇到这种情况，mysql to oracle时 text类型转到oracle自动变为BLOB,但这些字段只需要CLOB.所以打算把BLOB转为CLOB。（在sqlplu或plsql中完成）

 

解决办法

例：表gw_gift 字段description BLOB

1、增加字段description1 CLOB

2、把BLOB列数据写入CLOB

update gw_gift p set p.description1=utl_raw.cast_to_varchar2(p.description) where DBMS_LOB.GETLENGTH(DESCRIPTION)>2000;

commit;

注：utl_raw.cast_to_varchar2（） 转换BLOB为RAW，再转入CLOB

       DBMS_LOB.GETLENGTH（）取得BLOB内容的长度。因为BLOB转RAW时超过2000会报“ORA-22835:缓冲区对于BLOB到RAW转换而言太小(实际:4366,最大2000)”

当遇到BLOB值大于2000时的解决办法：

一、如果大于2000的行不多，先转换小于2000的，再手工修改大于2000的。

二、利用function函数完成

       1.新建BLOB to CLOB的function

 CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
   AS
    v_clob    CLOB;
    v_varchar VARCHAR2(32767);
    v_start PLS_INTEGER := 1;
    v_buffer PLS_INTEGER := 32767;
    tmp_num number;
    BEGIN
        DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); 
        tmp_num := CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer);
        if tmp_num > 0 then --防止传入的BLOB为NULL或长度为0时引发错误
          FOR i IN 1..tmp_num
          LOOP
            v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start)); 
            DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
             v_start := v_start + v_buffer;
          END LOOP;
        end if; 
        RETURN v_clob; 
    END blob_to_clob;

   2.调用blob_to_clob 来完成转换

 update gs_gift p set p.description1=blob_to_clob(p.description);
 commit;

 

3、删除description列，修改CLOB的description1 列名为description

完成！

 

 

 

 

 

 

 

 

 

 

列：

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
   AS
    v_clob    CLOB;
    v_varchar VARCHAR2(32767);
    v_start PLS_INTEGER := 1;
    v_buffer PLS_INTEGER := 32767;
    tmp_num number;
    BEGIN
        DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); 
        tmp_num := CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer);
        if tmp_num > 0 then --防止传入的BLOB为NULL或长度为0时引发错误
          FOR i IN 1..tmp_num
          LOOP
            v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start)); 
            DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
             v_start := v_start + v_buffer;
          END LOOP;
        end if; 
        RETURN v_clob; 
    END blob_to_clob;
commit;

--GS_AUTHORIZATIONAPPLY
ALTER   TABLE   GS_AUTHORIZATIONAPPLY   ADD   AUDITREMARK1 CLOB;--新增
update GS_AUTHORIZATIONAPPLY g set g.AUDITREMARK1=blob_to_clob(g.AUDITREMARK);--转换
commit;
alter table GS_AUTHORIZATIONAPPLY drop column AUDITREMARK;--删除
alter table GS_AUTHORIZATIONAPPLY rename column AUDITREMARK1 to AUDITREMARK;--重命名列

--GS_CUSTOMER
ALTER   TABLE   GS_CUSTOMER   ADD   INTRO1 CLOB;--新增
update GS_CUSTOMER g set g.INTRO1=blob_to_clob(g.INTRO);--转换
commit;
alter table GS_CUSTOMER drop column INTRO;--删除
alter table GS_CUSTOMER rename column INTRO1 to INTRO;--重命名列

--GS_CUSTOMERCONTACTTASK
ALTER   TABLE   GS_CUSTOMERCONTACTTASK   ADD   INTRO1 CLOB;--新增
update GS_CUSTOMERCONTACTTASK g set g.INTRO1=blob_to_clob(g.INTRO);--转换
commit;
alter table GS_CUSTOMERCONTACTTASK drop column INTRO;--删除
alter table GS_CUSTOMERCONTACTTASK rename column INTRO1 to INTRO;--重命名列

--GS_CUSTOMERVISITRECORD
ALTER   TABLE   GS_CUSTOMERVISITRECORD   ADD   INTRO1 CLOB;--新增
update GS_CUSTOMERVISITRECORD g set g.INTRO1=blob_to_clob(g.INTRO);--转换
commit;
alter table GS_CUSTOMERVISITRECORD drop column INTRO;--删除
alter table GS_CUSTOMERVISITRECORD rename column INTRO1 to INTRO;--重命名列

--GS_DIARY
ALTER   TABLE   GS_DIARY   ADD   CONTENT1 CLOB;--新增
update GS_DIARY g set g.CONTENT1=blob_to_clob(g.CONTENT);--转换
commit;
alter table GS_DIARY drop column CONTENT;--删除
alter table GS_DIARY rename column CONTENT1 to CONTENT;--重命名列

--gs_gift
ALTER   TABLE   gs_gift   ADD   description1 CLOB;--新增
update gs_gift g set g.description1=blob_to_clob(g.description);--转换
commit;
alter table gs_gift drop column description;--删除
alter table gs_gift rename column description1 to description;--重命名列

--gw_gift
ALTER   TABLE   gw_gift   ADD   description1 CLOB;--新增
update gw_gift g set g.description1=blob_to_clob(g.description);--转换
commit;
alter table gw_gift drop column description;--删除
alter table gw_gift rename column description1 to description;--重命名列

 

drop function blob_to_clob;--删除function

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－