ORACLE批量修改Number精度

declare
   tableName varchar(100);
   columnName varchar(100);
   commentsName varchar(500);
   scaleLength varchar(10);
   tempSqlStr varchar(500);
   updateSqlStr1 varchar(500);
   dropOldSqlStr varchar(500);
   createNewSqlStr varchar(500);
   updateSqlStr2 varchar(500);
   dropTempSqlStr varchar(500);
   commentsSqlStr varchar(500);
begin
  for c in (select * from user_tab_columns where DATA_TYPE = 'NUMBER' and DATA_PRECISION is not null
and TABLE_NAME in('TABLE_NAME')) loop
     dbms_output.put_line('--------->'||c.table_name||'__'||c.COLUMN_NAME||'<---------');
     --赋值
     tableName := c.table_name;
     columnName:= c.COLUMN_NAME;
     scaleLength := c.data_scale;
     select UTCC.COMMENTS into commentsName from user_col_comments UTCC where UTCC.TABLE_NAME = tableName and UTCC.COLUMN_NAME = columnName;
     --创建临时字段
     tempSqlStr := 'alter table '||tableName||' add (temp_number_0509 number(16,'||scaleLength||'))';
     dbms_output.put_line('--------->'||tempSqlStr||'<---------');
     --将老字段值赋予临时字段
     updateSqlStr1 := 'update '||tableName||' set temp_number_0509 = '||columnName;
     dbms_output.put_line('--------->'||updateSqlStr1||'<---------');
     --删除老字段
     dropOldSqlStr := 'alter table '||tableName||' drop column '||columnName;
     dbms_output.put_line('--------->'||dropOldSqlStr||'<---------');
     --创建新字段
     createNewSqlStr := 'alter table '||tableName||' add ('||columnName||' number(16,'||scaleLength||'))';
     dbms_output.put_line('--------->'||createNewSqlStr||'<---------');
     --将临时字段值赋予新字段
     updateSqlStr2 := 'update '||tableName||' set '||columnName||' = temp_number_0509';
     dbms_output.put_line('--------->'||updateSqlStr2||'<---------');
     --删除临时字段
     dropTempSqlStr := 'alter table '||tableName||' drop column temp_number_0509';
     dbms_output.put_line('--------->'||dropTempSqlStr||'<---------');
     --添加备注
     commentsSqlStr := 'COMMENT ON COLUMN '||tableName||'.'||columnName||' IS '''||commentsName||'''';
     dbms_output.put_line('--------->'||commentsSqlStr||'<---------');
     --执行
     execute immediate tempSqlStr;
     execute immediate updateSqlStr1;
     execute immediate dropOldSqlStr;
     execute immediate createNewSqlStr;
     execute immediate updateSqlStr2;
     execute immediate dropTempSqlStr;
     execute immediate commentsSqlStr;
     tableName := '';
     columnName := '';
     scaleLength := '';
     tempSqlStr := '';
     updateSqlStr1 := '';
     dropOldSqlStr := '';
     createNewSqlStr := '';
     updateSqlStr2 := '';
     dropTempSqlStr := '';
     commentsName := '';
     commentsSqlStr := '';
  end loop;
end;