How to Debug Ora-01775: Looping Chain of Synonyms

How to debug ORA-01775: looping chain of synonyms?

As it turns out, the problem wasn't actually a looping chain of synonyms, but the fact that the synonym was pointing to a view that did not exist.

Oracle apparently errors out as a looping chain in this condition.

ORA-01775: looping chain of synonyms

It looks like the output is from running this a second time, which you hinted at; the ORA-00955 from the create public synonym shows that has been done before somewhere (as does the all_synonyms query you ran), and it clearly exists from the other errors. You wouldn't have got exactly these errors the first time you ran it, but would on every subsequent run.

At a mimimum the first code snippet should drop the public synonym before dropping the table, if you want it to be rerunnable.

But the first snippet doesn't seem to be run at all. There are no success or failure messages from Oracle. The only real clue to why is this:

Processing bns_saa_messages
cat: cannot open bns_saa_messages.sql

Which is a shell script problem, not really an Oracle one. Without seeing the shell script it's rather hard to tell quite what's wrong, but I suspect the script is building a temporary .sql file from various sources and then running it through SQL*Plus; but the vital bns_saa_messages.sql file is missing. Presumably that's where the first snippet is supposed to be; and since that seems to exist, this could beas simple as a name mismatch between the file and what the script is expecting, or the script is doing a cd and the file is in the wrong directory, or something equally trivial. But maybe not... not enough info.

Why Oracle is complaining about a looping chain of synonyms (ORA01775) in package compilation?

After updating to the Oracle Database 12c version, the error changed back to:

ORA-00942: table or view does not exist

If someone is experiencing that kind of exception, the answer is that it could be the synonym target missing and don't a loop synonym.

I hope it helps someone.

weird looping chain of synonyms error on plsql package

This bit doesn't look right, are you really trying to redefine your cursor iterator value?

...
    --write detail records
    for detail_record in recordset
    loop
        detail_record :=
            a || ',' || 
            b || ',' || 
            c || ',' ||
... 

I'm guessing that you really want something more like

procedure write_records ...
is
  detail_record varchar2(32000);
  ...
begin
  ...
  --write detail records
  for dr in in recordset loop
    detail_record := dr.a || ',' || dr.b || ',' ....

Grant command on a synonym throws ORA-01775 error

You are getting the error in case you have performed the following tasks:

CREATE OR REPLACE public SYNONYM TRG_TEST FOR TRG_TEST;
GRANT ALL ON TRG_TEST TO USER1; --this will give error

Error:
GRANT ALL ON TRG_TEST TO USER1
Error report -
ORA-01775: looping chain of synonyms
01775. 00000 -  "looping chain of synonyms"
*Cause:    
*Action:

If as suggested by @Frank Schmitt, you put the schema name before trigger name in synonym then also you will get the same error:

CREATE OR REPLACE public SYNONYM TRG_TEST FOR schema.TRG_TEST;
GRANT ALL ON TRG_TEST TO USER1; --this will give error

Error:
GRANT ALL ON TRG_TEST TO USER1
Error report -
ORA-01775: looping chain of synonyms
01775. 00000 -  "looping chain of synonyms"
*Cause:    
*Action:

Let me clear this, THIS ERROR MESSAGE IS MISLEADING.

You can create a synonym of almost any objects in oracle regardless of its type (table, view, sequence, etc). Oracle does not know what is the object type behind the synonym.

Now, In our case, if Oracle knows that the GRANT is going to be performed on TRIGGER then it will give a different error.

-- actual error
drop public synonym TRG_TEST;
GRANT ALL ON TRG_TEST TO USER1;

Error starting at line : 16 in command -
GRANT ALL ON TRG_TEST TO USER1
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

So the conclusion is: You cannot give grants for the trigger. There is no such thing. Triggers fire automatically whenever the trigger event is done on the table on which the trigger is created. You only need to grant privilege on the table.

Cheers!!

---

Related Topics