Oracle Synonym Error

Question

I am knew to programming against an Oracle DB and I am getting this error:

ORA-06550: line 2, column 9: PL/SQL: ORA-01775: looping chain of synonyms

Is this something wrong in my code or in my DB? The code is the same for pretty much the whole data access layer and I am only getting this error when I try to insert into one particular table.

BEGIN
UPDATE DEAL_NOTE 
SET NOTE_TXT=:prmNOTE_TXT 
WHERE DEAL_ID=:prmDEAL_ID;

IF SQL%NOTFOUND THEN
INSERT INTO DEAL_NOTE (NOTE_TXT, DEAL_ID) values (:prmNOTE_TXT, :prmDEAL_ID);
END IF;

END;

This is most likely an error in your SQL. Perhaps you could edit your post to include your SQL statement. — , Jul 15 '11 at 16:45

score 3 · Accepted Answer · answered Jul 15 '11 at 16:52

3

You have a problem in your database.

The ORA-01775 error indicates that you have one synonym A that references another synonym B. Synonym B, in turn, references synonym A. Something like

SQL> create synonym foo2 for foo1;

Synonym created.

SQL> create synonym foo1 for foo2;

Synonym created.

SQL> select * from foo2;
select * from foo2
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

The only way to correct this is to fix the synonyms in the database so that you don't have these sorts of circular dependencies. You can run a query to identify these synonym loops (assuming you just have a one-level loop where A points to B and B points to A rather than more involved cycles) with a query like this

SQL> ed
Wrote file afiedt.buf

  1  select a.synonym_name, b.synonym_name
  2    from all_synonyms a,
  3         all_synonyms b
  4   where a.owner = b.table_owner
  5     and a.synonym_name = b.table_name
  6     and b.owner = a.table_owner
  7*    and b.synonym_name = a.table_name
SQL> /

SYNONYM_NAME                   SYNONYM_NAME
------------------------------ ------------------------------
FOO2                           FOO1
FOO1                           FOO2

answered Jul 15 '11 at 16:52

Justin Cave

227,342
24
367
384

When I run that query I do not get any results, does this mean that I have an even larger problem? – esastincy Jul 15 '11 at 17:00
@esastincy - It likely means that you have more than a one-level cycle-- synonym A points to synonym B, B points to C, and C points to A. It's probably easier just to manually walk through the dependency tree-- see what the synonym you're using references in `ALL_SYNONYMS`, see what that synonym references, etc. until you find the cycle. – Justin Cave Jul 15 '11 at 17:03
You can also get this error if you create a synonym that references itself. This easily happens when you create a PUBLIC synonym for a particular schema's table/view, then drop/rename the table/view. – DCookie Jul 15 '11 at 17:04
@DCookie - How do you get an ORA-01775 by dropping a table that a synonym references? I can only get an ORA-00942: table or view does not exist error in that case. – Justin Cave Jul 15 '11 at 17:11
@Justin: create a table me.x. create a PUBLIC SYNONYM x FOR me.x. Drop the TABLE me.x. SELECT * FROM x; results the error. You really don't need the CREATE/DROP the table steps, either, it just illustrates how I've usually seen it happen. Just create a PUBLIC SYNONYM x for me.x. SELECT * FROM x. Same error. – DCookie Jul 15 '11 at 17:32
I get the ORA-00942 error in that case, not ORA-01775. create public synonym enp2_syn for scott.emp2; drop table emp2; select * from emp2_syn; ERROR at line 1: ORA-00942: table or view does not exist – Justin Cave Jul 15 '11 at 17:34
@JustinCave let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/1520/discussion-between-dcookie-and-justin-cave) – DCookie Jul 15 '11 at 17:44

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

This is probably a code problem - probably something to do with what you have on line2 of your script.

Care to post it?

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

http://ora-01775.ora-code.com/ suggests:

ORA-01775: looping chain of synonyms Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1

Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

edited May 23 '17 at 10:24

Community

1
1

answered Jul 15 '11 at 16:45

diagonalbatman

17,340
3
31
31

1

It's unlikely that the problem is related to the script. Line 2 column 9 is undoubtedly where the reference to the synonym occurs. The problem is the underlying definition of the synonym. – Justin Cave Jul 15 '11 at 16:53

score 0 · Answer 3 · answered Jul 15 '11 at 16:47

0

This is a problem in the DB -- probably the table you are touching is a synonym that indirectly references itself.

answered Jul 15 '11 at 16:47

antlersoft

14,636
4
35
55

Oracle Synonym Error

3 Answers3