点击(此处)折叠或打开

create or replace procedure p_di_tab_syn
is
lvc_di_tab_sql varchar2(4000);
cursor cur_di_tab is
select \'create or replace synonym \'||table_name||\' for \'||owner||\'.\'||table_name synonym_sql
from all_tables
where owner=\'J1_DI\' and (table_name like \'DI%\' or table_name like \'DM_%\')
order by owner,table_name;
begin
--创建di 用户下对应表同义词
for mycur_di_tab in cur_di_tab loop
lvc_di_tab_sql:=mycur_di_tab.synonym_sql;
execute immediate lvc_di_tab_sql;
end loop;
exception
when others then
dbms_output.put_line(\'创建同义词失败.\'||to_char(sysdate, \'yyyy-mm-dd hh24:mi:ss\')||\', sqlcode:\'||to_char(sqlcode)||sqlerrm);
end;

执行后，同义词并未创建，但是单独将SQL拿出来可以执行，经查，需要对执行创建的用户进行显示赋权才行，即

点击(此处)折叠或打开

赋权之后，执行完即创建了同义词

ITPub博客

存储过程执行EXECUTE IMMEDIATE没反应