點選(此處)摺疊或開啟

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拿出來可以執行，經查，需要對執行建立的使用者進行顯示賦權才行，即

點選(此處)摺疊或開啟

賦權之後，執行完即建立了同義詞

儲存過程執行EXECUTE IMMEDIATE沒反應

相關文章