oracle批量将clob字段转换为varchar2字段

问题背景：客户要求必须要用oracle，用工具将mysql库转为oracle库之后，存在部分clob属性的字段，需要转为varchar2

处理方式：通过如下存储过程即可，下述过滤条件记得调整 

create or replace procedure PROC_CLOB2VARCHAR2 as

  tabName       varchar2(200);
  columnName    varchar2(200);
  tmpColumnName varchar2(200);
  stmt          varchar2(500);
begin
dbms_output.ENABLE(buffer_size => null);

  for item in (SELECT t.*
                 FROM user_tab_columns t
                WHERE t.DATA_TYPE = 'CLOB'
                  AND t.TABLE_NAME like '%_DATA_VIEW%'  AND t.TABLE_NAME in(SELECT ut.TABLE_NAME FROM user_tables ut)
                ORDER BY t.COLUMN_NAME) loop

    tabName       := item.table_name;
    columnName    := item.column_name;
    tmpColumnName := item.column_name || '_NEW';

    EXECUTE IMMEDIATE ('ALTER TABLE ' || UPPER(tabName) ||
                      ' RENAME COLUMN ' || UPPER(columnName) || ' to ' ||
                      UPPER(tmpColumnName));
    EXECUTE IMMEDIATE ('ALTER TABLE ' || UPPER(tabName) || ' ADD ' ||
                      UPPER(columnName) || ' VARCHAR2(4000)');
    EXECUTE IMMEDIATE ('UPDATE ' || UPPER(tabName) || ' SET ' ||
                      UPPER(columnName) || ' = TRIM(' || tmpColumnName || ')');
    EXECUTE IMMEDIATE ('ALTER TABLE ' || UPPER(tabName) || ' DROP COLUMN ' ||
                      tmpColumnName);

    /* stmt := ('ALTER TABLE ' || UPPER(tabName) || ' RENAME COLUMN ' ||
    UPPER(columnName) || ' to ' || UPPER(tmpColumnName));*/

    /*  dbms_output.put_line(tabName);
    dbms_output.put_line(columnName);
    dbms_output.put_line(tmpColumnName);
    dbms_output.put_line(stmt);*/
    dbms_output.put_line(tabName || ':' || columnName);

  end loop;
  commit;
end;