ii varchar2(100); ii := null; 这两句去掉 for后面的参数不需要在前面定义 而且ii是游标,不是字符串
1楼的兄台 我去掉了你说的那两行 。执行还是出错 SQL> exec proc_update;begin proc_update; end;ORA-00922: missing or invalid option ORA-06512: at "local.PROC_UPDATE", line 22 ORA-06512: at line 1
改下 declare -- ii varchar2(100); t_number number; begin -- ii := null; ---判断表是否存在,如果存在DROP. for i in (select table_name from v_tables) loop select count(1) into t_number from user_tables where table_name = upper(i.table_name);
if t_number > 0 then dbms_output.put_line('drop table ' || upper(i.table_name)); --execute immediate 'drop table ' || upper(ii.table_name); end if; end loop; ---根据远程数据库创建控制表v_tables中的表. for j in (select table_name from v_tables where isupdate = '1' ) loop dbms_output.put_line('create table ' || upper(j.table_name) || 'as select * from ' || upper(j.table_name) || '@toasia where rownum = 1'); /*execute immediate 'create table ' || upper(j.table_name) || 'as select * from ' || upper(j.table_name) || '@toasia where rownum = 1'; */ end loop; end; 你先看看output的结果
ii := null;
这两句去掉
for后面的参数不需要在前面定义
而且ii是游标,不是字符串
SQL> exec proc_update;begin proc_update; end;ORA-00922: missing or invalid option
ORA-06512: at "local.PROC_UPDATE", line 22
ORA-06512: at line 1
declare
-- ii varchar2(100);
t_number number; begin -- ii := null;
---判断表是否存在,如果存在DROP.
for i in (select table_name from v_tables) loop
select count(1)
into t_number
from user_tables
where table_name = upper(i.table_name);
if t_number > 0 then
dbms_output.put_line('drop table ' || upper(i.table_name));
--execute immediate 'drop table ' || upper(ii.table_name);
end if;
end loop;
---根据远程数据库创建控制表v_tables中的表.
for j in (select table_name
from v_tables
where isupdate = '1'
) loop
dbms_output.put_line('create table ' || upper(j.table_name) ||
'as select * from ' || upper(j.table_name) ||
'@toasia where rownum = 1');
/*execute immediate 'create table ' || upper(j.table_name) ||
'as select * from ' || upper(j.table_name) ||
'@toasia where rownum = 1'; */
end loop;
end;
你先看看output的结果
在execute immediate之前最好先输出拼好的字符串,检查确定无误再执行