set echo off feedback off verify off pagesize 0 linesize 120
---变量从 sqlplus 的 call代码 传递过来 。
-- 1 表示连接用户
-- 2表示连接用户密码
-- 3表示连接字符串
---4表示授权用户
define v_input_un = &1
define v_input_pw = &2
define v_input_conn_str = &3
define v_input_owner = &4
connect &v_input_un/&v_input_pw@&v_input_conn_str
define v_create_syn_command_file = .\log\create_syn_&v_input_un._&v_input_owner..sql
define v_create_syn_log_file = .\log\create_syn_&v_input_un._&v_input_owner..log
-- not exists 就是判断语句，如果没有子条件数据返回，就继续查询。
spool &v_create_syn_command_file.
prompt spool &v_create_syn_log_file.
prompt set echo on feedback on
prompt show user
select
'create or replace synonym '|| o.object_name ||' for &v_input_owner..'||o.object_name||';'
from all_objects o
where o.owner = upper('&v_input_owner')
and user != upper('&v_input_owner')
and o.object_type in ('TABLE', 'VIEW', 'SEQUENCE', 'PACKAGE', 'PROCEDURE', 'FUNCTION')
and not exists
(select null
from user_synonyms s
where s.table_owner = o.owner
and s.table_name = o.object_name)
order by o.object_name
/
select
'create or replace synonym '|| a_s.synonym_name ||' for &v_input_owner..'||a_s.synonym_name||';'
from all_synonyms a_s
where a_s.owner = upper('&v_input_owner')
and user != upper('&v_input_owner')
and not exists
(select null
from user_synonyms u_s
where u_s.table_owner = a_s.owner
and u_s.table_name = a_s.synonym_name)
order by a_s.synonym_name
/
prompt spool off
spool off
@&v_create_syn_command_file.