I've gone thru' the following thread which already discussed the same error :
How to debug ORA-01775: looping chain of synonyms?
But I couldn't comment as I'm a newbie and hence this question here :
In my case, I've an user B which has a package P1 which references a package P2 owned by user R (R.P2). R has granted the execute privilege on P2 to B. R has granted the same privilege to a few other users G1, G2 & G3. G1, G2 & G3 all have private synonyms to this package R.P2 in their own schemas, which look good.
Then, I created a public synonym to this package R.P2 with the same name P2 and tried to compile the package B.P1, I get the above ORA-1775 error.
Upon checking, dba_synonyms look good. But in dba_dependencies, I see two rows as follows :
OWNER NAME TYPE REF_OWNER REF_NAME REFERENCED_TYPE REFERENCED_LINK DEPE
------ ----- -------- --------- --------- --------------- --------------- ----
PUBLIC P2 SYNONYM R P2 NON-EXISTENT HARD
PUBLIC P2 SYNONYM PUBLIC P2 SYNONYM HARD
But, for the users G1, G2 & G3, I see the 1 row each in dba_dependencies which look perfect :
OWNER NAME TYPE REF_OWNER REF_NAME REFERENCED_TYPE REFERENCED_LINK DEPE
------ ----- -------- --------- --------- --------------- --------------- ----
G1 P2 SYNONYM R P2 PACKAGE HARD
G2 P2 SYNONYM R P2 PACKAGE HARD
G3 P2 SYNONYM R P2 PACKAGE HARD
I see the package P2 (both spec & body) very much there in R's schema and valid.
I tried to drop the public synonym and tried to create a private synonym instead in user B's schema, similar to the ones in G1, G2 and G3 users's schemas. I still see two rows in dba_dependencies with both the rows have reference_type = NON-EXISTENT.
Not sure if there're anything else missing here.
Any suggestions will be highly appreciated.
Thanks
Tried to compile a package which resulted in the above error. Tried both public and private synonyms but they didn't work.
