Oracle:是否可以为模式创建同义词? [英] Oracle: is it possible to create a synonym for a schema?

问题描述

我是oracle新手，我没有本地oracle专家来帮助我.

I am an oracle newbie, and I don't have a local oracle guru to help me.

我有一些SQL脚本必须发布到许多Oracle实例中. 脚本创建存储过程.
在其中创建存储过程的模式不同于包含从中读取存储过程的表的模式.

I have some SQL scripts which have to be released to a number of Oracle instances. The scripts create stored procedures.
The schema in which the stored procedures are created is different from the schema which contains the tables from which the stored procedures are reading.

在不同的实例上，包含表的模式具有不同的名称.

On the different instances, the schema containing the tables has different names.

很明显，我不想编辑脚本以使它们针对不同的实例进行定制.

Obviously, I do not want to have to edit the scripts to make them bespoke for different instances.

有人向我建议解决方案可能是建立同义词.

It has been suggested to me that the solution may be to set up synonyms.

是否可以在每个实例上为表模式定义同义词，并在脚本中使用该同义词?

Is it possible to define a synonym for the table schema on each instance, and use the synonym in my scripts?

还有其他方法可以使这项工作无需每次都编辑脚本吗?

Are there any other ways to make this work without editing the scripts every time?

感谢您的帮助.

推荐答案

了解Oracle版本是有帮助的，但是

It'd help to know what version of Oracle, but as of 10g--No, you can't make a synonym for a schema.
You can create synonyms for the tables, which would allow you not to specify the schema in the scripts. But it means that the synonyms have to be identical on every instance to be of any use...

另一个选择是用变量替换模式引用，因此在脚本运行时会提示用户输入模式名称.我更喜欢这种方法，因为它工作量少.这是一个可以在SQLPlus中工作的示例:

The other option would be to replace the schema references with variables, so when the script runs the user is prompted for the schema names. I prefer this approach, because it's less work. Here's an example that would work in SQLPlus:

CREATE OR REPLACE &schema1..vw_my_view AS
  SELECT *
    FROM &&schema2..some_other_table

这样做的好处是，运行脚本的人员只会为每个变量提示一次，而不是每次遇到该变量时都被提示.所以要小心错别字:)

The beauty of this is that the person who runs the script would only be prompted once for each variable, not every time the variable is encountered. So be careful about typos :)

这篇关于Oracle:是否可以为模式创建同义词?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！