CODEWITHSUNDEEP
c#sqloracle-database
esastincy
esastincy

Reputation: 1627

Oracle Synonym Error

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;

Upvotes: 3

Views: 1347

Answers (3)

Justin Cave
Justin Cave

Reputation: 231681

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

Upvotes: 3

antlersoft
antlersoft

Reputation: 14786

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

Upvotes: 0

diagonalbatman
diagonalbatman

Reputation: 18002

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.

Upvotes: 0

Related Questions