Post reply

DB Link from Oracle to SQL Server 2014 Issues

  • February 13, 2018 at 12:41 pm

    #403111

    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! 

  • February 21, 2018 at 11:43 am

    #1980663

    The fix for this was to modify some values in the gateway init.ora file (an Oracle config file)

    Particularly, give values to HS_DB_NAME and HS_DB_DOMAIN parameters and then use those values as the name for a db link.

    So we put
    HS_DB_NAME myDBName <1-8 characters>
    HS_DB_DOMAIN SQLSERVAD <1-119 characters>
    and created a db link using those values .... meaning that the NAME of the db link is, using this example, "myDBName.SQLSERVAD"...no quotes, of course. Internals of the db link have connection to the actual database and sql server. But what we needed was a name that Oracle could grab onto.

    Now, I can do an insert on a table UserInfo in SQL Server database like
    insert into UserInfo@myDBName.SQLSERVAD (fname, lname, nickname) values ('Mike','Hanson','Mikie')
    and it works.

    The default value of HS_DB_NAME is "HO"
    The default value of HS_DB_DOMAIN is "WORLD"

    So when you get an error saying that the db link connects to HO.WORLD then you know you haven't got these parameters set correctly for your db link.

    Once this is all set up correctly, you can create and use a db link without getting that error and without having to kludge a fix by altering session to set global_names=false.

Viewing 2 posts - 1 through 1 (of 1 total)

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