I am in a position which requires I pull data to my primary database through a daisy chained dblink set up similar to what is described in this answer
DBLINK_SERVER2 -> SYNONYM -> DBLINK_SERVER3
The synonym is set up on server 2 like this:
CREATE OR REPLACE SYNONYM "MY_USER"."THE_VIEW" FOR "THE_VIEW"@"DBLINK_SERVER3"
When I run a simple select statement from server 1, it works fine and pulls back exactly what i'd expect from server 3's view:
SELECT COUNT(*) FROM THE_VIEW@DBLINK_SERVER2;
However, whenever I try to run code in an anonymous block any reference to the dblink will cause a "looping chain of synonyms" error.
DECLARE
testnum VARCHAR2(50);
BEGIN
SELECT COUNT(*) INTO testnum FROM THE_VIEW@DBLINK_SERVER2;
END;
/
Error report -
ORA-06550: line 2, column 22:
PL/SQL: ORA-01775: looping chain of synonyms
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
There aren't any synonyms on server 1 that i can see which reference the view or dblink. If i run this same test on server 2 both the simple command and block work fine when i use the synonym. Any idea why the block fails on server 1 but not if i run the same thing outside of the block? Is this some kind of permissions problem?
*** Edit ***
We never determined what was causing this, but the DBA changed it so that we were referencing a view in server 2 instead of a symlink. That resolved our problem.
CREATE VIEW THE_VIEW AS SELECT * FROM THE_VEW@DBLINK_SERVER3
