直接引用Oracle中的公共同义词

I know this is backwards, but say we have an inflexible tool or situation which always creates SELECT statements in the form

我知道这是反向的，但是我们有一个不灵活的工具或情况，它总是在表单中创建SELECT语句

SELECT * FROM ${SCHEMA}.${TABLE};

Now say we have a public synonym for table called MY_TABLE but no actual MY_TABLE in the current schema. That is our user is USER but the synonym for MY_TABLE points to OTHER_USER.MY_TABLE.

现在，我们有一个名为MY_TABLE的表的公共同义词，但在当前模式中没有实际的MY_TABLE。我们的用户是user，但MY_TABLE的同义词指向OTHER_USER.MY_TABLE。

Note that this works fine as expected: select * from MY_TABLE UNION ALL select * from OTHER_USER.MY_TABLE;

注意，这工作得很好:从MY_TABLE UNION中选择*，所有从OTHER_USER.MY_TABLE中选择*;

I'd like to do something like

我想做一些类似的事情

SELECT * FROM SYNONYMS.${TABLE};

Is there anything we can place in ${SCHEMA} that will force Oracle to find and resolve the synonym? I've tried the following literal SQL statements to try to refer to the synonym directly and they don't work.

在${SCHEMA}中有什么东西可以强制Oracle查找和解析同义词吗?我尝试了下面的字面SQL语句，试图直接引用同义词，但它们不起作用。

select count(*) from USER.MY_TABLE; 

select count(*) from CURRENT_SCHEMA.MY_TABLE;

select count(*) from .MY_TABLE;

select count(*) from %.MY_TABLE;

Note that we must refer to the synonym because we change the synonym frequently to help with production installs therefore I can't just place OTHER_USER in $SCHEMA because it could be OTHER_USER or OTHER_USER2 or OTHER_USER3 for reasons outside of the control of my code.

注意，我们必须引用同义词，因为我们经常更改同义词以帮助生产安装，因此我不能将OTHER_USER放在$SCHEMA中，因为出于代码控制之外的原因，它可以是OTHER_USER或OTHER_USER2或OTHER_USER3。

3 个解决方案

#1

You can use the synonym owner PUBLIC as the reference schema; as USERA:

您可以使用同义词所有者公共作为参考模式;USERA:

create table t42 (id number);
create public synonym t42 for t42;
grant select on t42 to userb;

Then as USERB:

然后USERB:

select * from "PUBLIC"."T42";

no rows selected

But it seems like the 'schema' has to be quoted:

但似乎必须引用“模式”:

select * from public.t42;
select * from public.t42
              *
ERROR at line 1:
ORA-00903: invalid table name


select * from "PUBLIC".t42;

no rows selected

Which may or may not be possible for you. If you can make ${SCHEMA} equals "PUBLIC", with the double-quotes, then it may solve your problem.

这对你来说是可能的，也可能是不可能的。如果您可以使用双引号使${SCHEMA}等于“PUBLIC”，那么它可能会解决您的问题。

I'm not sure why this has to be quoted, upper-case identifiers usually don't; but then PUBLIC isn't a normal user, so maybe it shouldn't be surprising that it needs special handling.

我不确定为什么要引用它，大写标识符通常不会;但是PUBLIC并不是一个普通的用户，所以它需要特殊的处理也就不足为奇了。

#2

I don't really understand your underlying motivation, but as I see it you may consider using a dedicated user schema and creating views within that schema that refer to the eventual object. Views can be used as symbolic links.

我不太理解您的基本动机，但是我认为您可以考虑使用专用的用户模式并在该模式中创建指向最终对象的视图。视图可以用作符号链接。

Why can't:

为什么不能:

 select count(*) from USER.MY_TABLE;

refer to a view in USER's schema named MY_TABLE that refers to whatever table is currently needed. You can recreate the view instead of repointing a synonym.

引用用户模式中名为MY_TABLE的视图，该视图引用当前需要的任何表。您可以重新创建视图，而不是重新指向同义词。

create view USER.MY_TABLE
   as select * from OTHER_USER.MY_TABLE;

#3

As far as I can tell there doesn't seem to be a literal value we can use for ${SCHEMA}. However, we do have the flexibility to find the schema owner this way, save the value, then use that value in ${SCHEMA}.

据我所知，${SCHEMA}似乎没有一个可以使用的文字值。但是，我们可以灵活地以这种方式查找模式所有者，保存值，然后在${schema}中使用该值。

select TABLE_OWNER from all_synonyms where TABLE_NAME='MY_TABLE' and OWNER='PUBLIC';

This query properly returns OTHER_USER, which we can then use.

这个查询正确地返回OTHER_USER，我们可以使用它。

#1