We have a table owned by User1.
An after update trigger on that table is owned by User2. That trigger executes a package.procedure also owned by User2.
No problem. Works fine.
Except when there is a reference to a db link to SQL Server in any of the procedures in the package. Then the update to the table fails with:
Error: ORA-04052: error occurred when looking up remote object <SQL_LOGIN.SQLTABLE@SQL_DB.fullyQualifiedDN_of_SQL_Box> (pseudo-names)
ORA-00604: error occurred at recursive SQL level 1
ORA-02085: database link
The following pretty much sums up all that the researching I've been doing for the last week suggests:
ORA-02085: database link string connects to string. Cause: a database link connected to a database with a different name. The connection is rejected. Action: create a database link with the same name as the database it connects to, or set global_names=false.
When we set global_names=false that remedies the problem. We can do that manually and see it work. But when we try to embed an alter session in either the package or the trigger, we get errors that alter session is not allowed in this context. It may have something to do with the fact that User2 can't alter session for User1. Or it may not.
I don't think that alter session is the real (correct) fix here. Rather, the fix is to create a db link with the same name as the database it connects to. Has anyone successfully done this?
The SID in TNSNames file has the value SQL_DB when compared to <SQL_LOGIN.SQLTABLE@SQL_DB.fullyQualifiedDN_of_SQL_Box> this string that it's looking for. Is our fix to modify the value of the SID, and if so, what should it include from this string? Or is that not the fix? Somehow we need to provide the "global_name" that makes Oracle happy with using this db link.
Thanks for any help!