create or replace procedure modifychartovarchar is --
cursor c1 is
select t.TABLE_NAME, t.COLUMN_NAME, t.DATA_LENGTH
from all_tab_columns t
where t.OWNER = 'LANDUSER' and t.DATA_TYPE = 'CHAR' and
t.TABLE_NAME in (select at.table_name from all_tables at);
PTABLE_NAME varchar2(100);
pCOLUMN_NAME varchar2(100);
sqlstr varchar2(200);
Dl integer;
begin
--by 闫磊 Email:Landgis@126.com,yanleigis@21cn.com 2006.10.11
open c1;
fetch c1
into PTABLE_NAME, pCOLUMN_NAME, Dl;
while c1%found loop
sqlstr := 'alter table ' || PTABLE_NAME || ' modify ' || pCOLUMN_NAME ||
' varchar2(' || Dl || ')';
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
sqlstr := 'update ' || PTABLE_NAME || ' set ' || pCOLUMN_NAME ||
'=trim(' || pCOLUMN_NAME || ')';
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
fetch c1
into PTABLE_NAME, pCOLUMN_NAME, Dl;
end loop;
commit;
close c1;
end;