哪里出错了。declare
tname varchar2(60);
cname varchar(100);
dlength number;
maxlength number;
begin
maxlength := 0;
for idx in (select u.TABLE_NAME,
u.COLUMN_NAME,
u.DATA_LENGTH,
decode(mod(u.DATA_LENGTH, 2), 0, 'EVEN', 'ODD') ODD_EVEN
from user_tab_columns u
where u.DATA_TYPE = 'VARCHAR2'
and u.TABLE_NAME in
(select t.table_name
from all_tables t
where t.num_rows > 0
and t.owner = 'BXDB')) loop
tname := idx.TABLE_NAME;
cname := idx.COLUMN_NAME;
dlength := idx.DATA_LENGTH;
execute immediate 'select max(lengthb(' || cname || ')) from ' || tname
into maxlength;
if (1.5 * maxlength) > dlength then
if idx.odd_even = 'ODD' then
dbms_output.put_line('alter table ' || tname || ' modify ' || cname || 'varchar2(' || 2 * maxlength || ');');
else
dbms_output.put_line('alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 1.5 * maxlength || ');');
end if;
end if;
end loop;
end;
tname varchar2(60);
cname varchar(100);
dlength number;
maxlength number;
begin
maxlength := 0;
for idx in (select u.TABLE_NAME,
u.COLUMN_NAME,
u.DATA_LENGTH,
decode(mod(u.DATA_LENGTH, 2), 0, 'EVEN', 'ODD') ODD_EVEN
from user_tab_columns u
where u.DATA_TYPE = 'VARCHAR2'
and u.TABLE_NAME in
(select t.table_name
from all_tables t
where t.num_rows > 0
and t.owner = 'BXDB')) loop
tname := idx.TABLE_NAME;
cname := idx.COLUMN_NAME;
dlength := idx.DATA_LENGTH;
execute immediate 'select max(lengthb(' || cname || ')) from ' || tname
into maxlength;
if (1.5 * maxlength) > dlength then
if idx.odd_even = 'ODD' then
dbms_output.put_line('alter table ' || tname || ' modify ' || cname || 'varchar2(' || 2 * maxlength || ');');
else
dbms_output.put_line('alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 1.5 * maxlength || ');');
end if;
end if;
end loop;
end;
解决方案 »
- 大数据量计算位置
- ORACLE利用触发器去更新另一张表的字段
- ORACLE存储过程中使用变量的问题
- 高手帮忙看个问题啊!!谢了
- 根据表tab2的quhao,统计出表tab1中相同地区(区号)的数量
- 请问ORACLE8I FOR WIN200SERVER中的两上"NET8"如何用?
- 请问怎样用SQL语句关闭和恢复表之间的约束关系?
- 一个很简单的存储过程就是编译不过去,请各位高手看看。在线等待
- 数据库的服务起不来 怎么删除(从服务里)有可以不重新安装的办法吗? 在线
- oracle biee 新建的仪表盘提示不起作用
- 登录应用时提示:已临时禁用到服务器的连接,连的是oracle数据库,哪位大侠知道是怎么回事?
- 关于Oralce 11g 安装的问题。
'select max(lengthb(' || cname || ')) from ' || tname
这一段代码, cname 前后需要加单引号
或者你使用绑定变量 execute immediate 后接 using
其它测试执行没有语法错误
cname || 'varchar2(' || 1.5 * dlength || ');'); 该应是dlength ,我写错了,不过这个是业务问题。谢谢你。caoleione 的回复:]应该是output输出的的太多 缓存不够造成的吧
[/Quote]
--测试了一下没问题,你想直接spool的话,可以将拼出的直接按照下面的方法执行
declare
tname varchar2(60);
cname varchar(100);
dlength number;
maxlength number;
begin
maxlength := 0;
for idx in (select u.TABLE_NAME,
u.COLUMN_NAME,
u.DATA_LENGTH,
decode(mod(u.DATA_LENGTH, 2), 0, 'EVEN', 'ODD') ODD_EVEN
from user_tab_columns u
where u.DATA_TYPE = 'VARCHAR2'
and u.TABLE_NAME in
(select t.table_name
from all_tables t
where t.num_rows > 0
and t.owner = 'BXDB')) loop
tname := idx.TABLE_NAME;
cname := idx.COLUMN_NAME;
dlength := idx.DATA_LENGTH;
execute immediate 'select max(lengthb(' || cname || ')) from ' || tname
into maxlength;
if (1.5 * maxlength) > dlength then
if idx.odd_even = 'ODD' then
dbms_output.put_line('alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 2 * maxlength || ');');
else
-- dbms_output.put_line('alter table ' || tname || ' modify ' ||
-- cname || 'varchar2(' || 1.5 * maxlength || ');');
execute immediate 'alter table ' || tname || ' modify ' ||
cname || 'varchar2(' || 1.5 * maxlength ); --这里直接执行就完了呗
end if;
end if;
end loop;
end;