DB Link from Oracle to SQL Server

Question

DB Link from Oracle to SQL Server

Michael Gerholdt

SSCommitted

Points: 1907
More actions
February 13, 2018 at 9:00 am

#333588

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Michael Gerholdt - Tuesday, February 13, 2018 9:00 AM
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!

Try following the instructions in this post:
ORA-02085: Database link created successfully but does not work

Sue

Michael Gerholdt SSCommitted Points: 1907 More actions · Answer 2

Thanks, Sue. These are good directions, but they resolve the issue in the context of Oracle to Oracle db link. It is pretty straightforward to obtain or set global names in that context.

I am trying to find out how to establish what to use in Oracle db link settings for values for Sql Server to satisfy it's global names scenario.

Michael Gerholdt SSCommitted Points: 1907 More actions · Answer 3

I didn't realize this was under Sql Server 2005. I reposted the question in the 2014 dev area.

Thanks