哪里出错了。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;
'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;