Q: DBlink & packages

I'm having a problem connecting to a remote db from a stored procedure. I have the following databases:

PROD (local)
TEST (local)
TEST2 (local)
AWAY (remote)

• PROD, TEST and TEST2 are different instances running on the same machine (Oracle 7.3).
• AWAY is running on a remote machine (Oracle 7.0.16).
• On TEST2, there's a dblink to AWAY.
• On PROD and TEST there is a dblink to TEST2.
• On PROD and TEST there are synonyms for tables on AWAY. The synonyms point at TEST2.tablename but TEST2.tablename is really a synonym for tablename_at_AWAY. So when issuing a select from PROD or TEST on a table residing on AWAY, I can merely write SELECT * FROM TABLENAME. Behind the scenes, I am really selecting from AWAY through TEST2.
• If I write SELECT * FROM TABLENAME from within SQL-PLUS, I get a correct result - no matter if I'm on PROD or on TEST.

In other words: PROD/TEST -> TEST2 -> AWAY

Now comes the weird part. The dblinks and the synonyms seem to work fine since I can select from the remote tables from both PROD and TEST (in SQLPLUS). On PROD, I have a stored procedure which selects from tables on AWAY. This works fine. But when I try to compile the same procedure (using the same user) on TEST, I get the following error:

(abreviated) ORA-04054: dblink *SOME_SID.WORLD does not exist.

• The sid is problably the name of the dblink from TEST2 to AWAY. It doesn't appear in tnsnames though.

The user has dba on both TEST and PROD. I know this is a complex problem so I really hope I made myself clear and that one of you gurus out there can help me out. I know that things could have been set up in a better way but for a number of reason, this is the way it has to be.

Any help appreciated.
- Ivan Bajon
Received on Mon Aug 17 1998 - 04:41:21 CDT