CODEWITHSUNDEEP
databaseoracle-databaseschemasynonym
bot403
bot403

Reputation: 2162

Directly refer to a public synonym in Oracle

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

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.

Note that this works fine as expected: select * from MY_TABLE UNION ALL select * from 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.

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.

Upvotes: 1

Views: 3293

Answers (3)

Alex Poole
Alex Poole

Reputation: 191295

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

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

Then as 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.

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.

Upvotes: 3

bot403
bot403

Reputation: 2162

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}. 

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.

Upvotes: 0

mrjoltcola
mrjoltcola

Reputation: 20842

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.

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

Upvotes: 0

Related Questions