本文介绍了一种使用PL/SQL块来动态生成并执行SQL语句的方法，该方法用于批量创建Oracle数据库中的同义词（Synonym）。通过查询DBA_OBJECTS视图筛选出特定所有者的表，并为这些表创建相应的同义词。
begin
for arr in (
select 'create or replace synonym ' || b.OBJECT_NAME || ' for ' || B.OWNER || '.' ||
B.OBJECT_NAME row_i from dba_objects B where OWNER = 'APPS' AND object_type = 'TABLE'
) loop
begin
execute immediate (arr.row_i);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(arr.row_i);
end;
end loop;
end;
for arr in (
select 'create or replace synonym ' || b.OBJECT_NAME || ' for ' || B.OWNER || '.' ||
B.OBJECT_NAME row_i from dba_objects B where OWNER = 'APPS' AND object_type = 'TABLE'
) loop
begin
execute immediate (arr.row_i);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(arr.row_i);
end;
end loop;
end;
扫一扫
1471
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
