1

I really need to understand why the behavior below is happening.

First, I just create a synonym:

CREATE SYNONYM SYN_NAME FOR MY_TABE@MY_DBLINK;

Second, when I try to select the rows from the table, the following error appears:

ORA-00942: table or view does not exist

Great, the table really does not exist.

However when I try to compile a Package that is using the synonym above, the compilation error is:

ORA-01775: looping chain of synonyms

There are no synonyms with the same name, cycling synonyms or database link/synonym conflicts in the entire database. Of course that when I create the table in the target database link schema, the errors runaway.

Could some one explain me why the errors aren't the same?

gustavodidomenico
  • 4,640
  • 1
  • 34
  • 50
  • Does this apply?http://stackoverflow.com/questions/247090/how-to-debug-ora-01775-looping-chain-of-synonyms – xQbert Apr 23 '14 at 18:07
  • Not really. I have been looking for answers before my post and I considered the one that you pointed out. – gustavodidomenico Apr 23 '14 at 18:10
  • The error is indicitive of not having permissions to do the select. The @my_DBLINK; accesses the remote database using it's own credentials. If that user doesn't have the proper permissions, it wouldn't be able to access the table, thus no table/view error and looping chain error. I think if you query all_objects using the userID on the link, if you don't see the table, that would likely be the problem. – xQbert Apr 23 '14 at 18:16
  • I appreciate your explanation, but both errors should be the same, when I compile the package or when I just run a select statement. For me the ORA01775 does not make sense. My question is just the reason that the errors are not the same. – gustavodidomenico Apr 23 '14 at 18:19
  • I'd have to defer to AskTom or similar oracle support for that. – xQbert Apr 23 '14 at 18:22

1 Answers1

1

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.

gustavodidomenico
  • 4,640
  • 1
  • 34
  • 50