locked
Unable to connect to remote Oracle database from a machine that does not have oracle client RRS feed

  • Question

  • User-1851346387 posted

    I have a dot net executable (build in Visual Studio 2008) that attempts to connect to remote oracle database from a machine that does not have oracle client installed. The error I am getting is

    "ORA-12154 TNS Could not resolve the connect identifier specified"

    from what I have read so far I needed to add reference Oracle.DataAccess.dll. Oracle.DataAccess was not available for adding under my VS 2008, so I went ahead and got a copy of MSI package (ODTwithODAC1020221.exe from Oracle site) to add the suggested DLL file (I am not sure if there is compatibility issue between my version of VS/.NET 3.5 SP1 and the download). After I have successfully added the reference, i get the above error.

    I can successfully do a TNSPING on a local machine that has oracle client. I have also validated database descriptions from TNSNAMES.ORA file.

    Here is the code I am using to connect to oracle:

    Imports System.IO
    Imports System.Xml
    Imports System.Net
    Imports System.Xml.Schema
    Imports System.Text
    Imports System.Data.Common.DataAdapter
    Imports System.Data.Common.DbDataAdapter
    Imports System.Net.Mail
    Imports System.Threading.Thread
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    Imports System.ServiceProcess
    Imports System.Management
    Imports System
    Imports Oracle.DataAccess
    Imports Oracle.DataAccess.Client
    
    
    
    Module Module1
    
    
        Public Sub Main()
    
            Dim sql As String
            Dim sql2 As String
    
               Dim DATABASENAME1 As String = "Data Source=(DESCRIPTION =" _
                + " (ADRESS = (PROTOCOL = TCP)(HOST=Server.Host.com)(PORT=1540))" _
                + ")" _
                + "(CONNECT_DATA = " _
                + "(SID=DATABASENAME1)));" _
                + "User Id=user;Password=password1;"
    
                 
            Dim conn1 As New OracleConnection()
             conn1.ConnectionString = DATABASENAME1
    
            '%%%%% Connect to database
    
            Try
                conn1.Open()
                Console.WriteLine("Connection to Oracle database established!")
                MsgBox("Connection to Oracle database established!")
    
                Console.WriteLine(" ")
    
    
            Catch ex As Exception
    
                MsgBox("Not Connected" & ex.Message)
    
                Console.WriteLine(ex.Message)
                End
            End Try
    END SUB
    END MODULE

    Any suggestion or direction greatly appreciated.

    Friday, January 8, 2016 5:33 PM

Answers

  • User-1851346387 posted

    This was a typo on my part, removed the extra close bracket and "ADDRESS"

    + ")" _
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2016 2:31 PM

All replies

  • User269602965 posted

    ORA-12154 TNS Could not resolve the connect identifier specified"

    That is a very broadly defined error that could be due to a long list of problems, like firewall not allowing the nonstandard port you are using.

    If you have an application on a remote box calling Oracle AND using the Oracle Unmanaged Driver (bit dependent), then you must install a compatible Oracle Client on that machine.  The driver can be in the /BIN folder of the application with a local reference and the tnsnames.ora will be in the NETWORK/ADMIN folder of the Client installation.

    If your environment does not have FIPS-140 enabled, you can use the Oracle MANAGED driver (bit independent), then the Oracle Client is built into the driver and you would need the correct managed Oracle driver and the tnsnames.ora in the /BIN dir of the application, and make a local reference to it.

    The application configuration parameters vary between managed and unmanaged, but fortunately other than IMPORT statement change, the code within the class is the same between the two styles of calling Oracle.

    Have you read the Oracle .NET Guide?

    Monday, January 11, 2016 10:21 PM
  • User-1851346387 posted

    This was a typo on my part, removed the extra close bracket and "ADDRESS"

    + ")" _
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 12, 2016 2:31 PM
  • User269602965 posted

    You might use Oracle Connection String Builder functionality as well to reduce risk of typos

    Tuesday, January 12, 2016 4:52 PM