declare
tbname varchar2(100);
cursor cursor_tbname is
select DISTINCT table_name
from user_tab_columns
where column_name = 'MQ_SENDTIME'
and table_name in (select table_name
from all_all_tables
where table_name like 'O_%'
or table_name like 'I_%'
and owner = 'CYMN');
begin
open cursor_tbname;
loop
fetch cursor_tbname into tbname;
exit when cursor_tbname%notfound;
execute immediate 'delete from table '||tbname|| ' where MQ_SENDTIME < todate( ''2012-12-31'')' ;
end loop;
close cursor_tbname;
END;结果
ORA-00903: 表名无效
ORA-06512: 在line 18不知道为什么 就修改方法
tbname varchar2(100);
cursor cursor_tbname is
select DISTINCT table_name
from user_tab_columns
where column_name = 'MQ_SENDTIME'
and table_name in (select table_name
from all_all_tables
where table_name like 'O_%'
or table_name like 'I_%'
and owner = 'CYMN');
begin
open cursor_tbname;
loop
fetch cursor_tbname into tbname;
exit when cursor_tbname%notfound;
execute immediate 'delete from table '||tbname|| ' where MQ_SENDTIME < todate( ''2012-12-31'')' ;
end loop;
close cursor_tbname;
END;结果
ORA-00903: 表名无效
ORA-06512: 在line 18不知道为什么 就修改方法
2、to_date函数,例如to_date('20130303','yyyymmdd'),没有什么todate函数
to_date('20130303','yyyymmdd'),在字符串里,单引号要转义