Forum OpenACS Q&A: Oracle, PL/SQL and synonyms

Collapse
Posted by Andrei Popov on

Sorry for a question which is somewhat off-topic.

I am having a strange set of problems with Oracle, specifically I cannot seem to execute pl/sql procedures if they are using tables that are synonym'ed via database link. Specifically, the following set up fails:


Server 1              Server 2              Server 3
Schema A              Schema B              Schema C
synA_tab1@b.world --> synB_tab1@c.world --> tab1

I can do both SELECT * FROM synA_tab1 and SELECT * FROM synB_tab1@b.world, but if I do something like this:

declare
  x number;
begin
  select count(1) into x
  from synA_tab1;
end;
/

I end up with an error like this:

ERROR at line 1:
ORA-04054: database link C.WORLD does not exist

To me it looks like "nested" synonyms cannot be used in pl/sql, is that correct, or I am doing something wrong?

Thanks.