none
How to Read the TNSNAMES.ora in VB.Net RRS feed

  • Question

  • Hello everybody.
    I'm working on an application in Visual Basic Windows Forms and Oracle.
    What I want to do it's to fill a combobox,  in my LogIn form,  with all the Oracle database names defined in the tnsnames.ora.
    If somebody knows how to do this.
    I want to locate the tnsnames.ora on every pc where my application will be running and I want to fill the combobox with the tnsnames databases names.
    If somebody can help me I really appreciate it.
    Thank's a lot!
    Monday, February 18, 2008 8:23 PM

Answers

  •  

    Hi Bryan.

    I founded a very good information at this site: http://dotnetjunkies.com/WebLog/debasish/archive/2005/11/13.aspx

    And I founded some other good information in here: http://visualbasic.ittoolbox.com/groups/technical-functional/vb-dotnet-l/parsing-an-oracle-tnsnamesora-file-526733

     

    Then, I took the from both with some little changes and I did this, I hope this help you.

    Notice that I made some changes to the RegExPattern in the public function Public Function LoadTNSNames to fit my tnsnames.ora.

    I have a form with a combobox and a button.

    In the click event from the button I added this code to test the class.

     

     

    Public Class Form1 ...

     

           Public sub button1_Click .....

                   Me.combobox1.datasource = New TNSNamesReader().LoadTNSNames()

          End Sub

     

    End class

     

    I hope this works for you.

    Best Regards,

     

    Douglas.

     

     

    Be cImports Microsoft.Win32
    Imports System.Text.RegularExpressions

     

    Public Class TNSNamesReader

         Private strOracleHome As String = ""
         Private strTNSNAMESORAFilePath As String = ""


         Private Function GetOracleHome() As String
              Dim rgkLM As RegistryKey = Registry.LocalMachine
              Dim rgkAllHome As RegistryKey = rgkLM.OpenSubKey("SOFTWARE\ORACLE\ALL_HOMES")

              If Not rgkAllHome.Equals("") Then
                   Dim strLastHome = ""
                   Dim objLastHome As Object = rgkAllHome.GetValue("LAST_HOME")
                   strLastHome = objLastHome.ToString()
                   Dim rgkActualHome As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\ORACLE\HOME" + strLastHome)
                   Dim strOraHome As String = ""
                   Dim objOraHome As Object = rgkActualHome.GetValue("ORACLE_HOME")
                   strOraHome = objOraHome.ToString()
                   strOracleHome = strOraHome
                   Return strOraHome
              Else
                   Return ""
              End If
         End Function

         Private Function GetTNSNAMESORAFilePath() As String
              If Not Me.GetOracleHome.Equals("") Then
                   strTNSNAMESORAFilePath = strOracleHome + "\NETWORK\ADMIN\TNSNAMES.ORA"
                   If Not (System.IO.File.Exists(strTNSNAMESORAFilePath)) Then
                        strTNSNAMESORAFilePath = strOracleHome + "\NET80\ADMIN\TNSNAMES.ORA"
                        Return strTNSNAMESORAFilePath
                   Else
                        Return strTNSNAMESORAFilePath
                   End If
              Else
                   Return ""
              End If
         End Function


         Public Function LoadTNSNames() As Collection
              Dim DBNamesCollection As New Collection
              Dim RegExPattern As String = "[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*=[\s]*\("

              GetTNSNAMESORAFilePath()
              'Adicionando una línea en blanco a la collection
              DBNamesCollection.Add("")
              If Not strTNSNAMESORAFilePath.Equals("") Then
                   Try
                        '//check out that file does physically exists
                        Dim fiTNS As New System.IO.FileInfo(strTNSNAMESORAFilePath)
                        If (fiTNS.Exists) Then
                             If (fiTNS.Length > 0) Then
                                  '//read tnsnames.ora file
                                  Dim iCount As Integer
                                  Try
                                       For iCount = 0 To Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Count - 1
                                            DBNamesCollection.Add(Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.Substring(0, Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.IndexOf(" ")))
                                       Next
                                  Catch ex As Exception
                                       Throw New Exception(ex.Message)
                                  End Try
                             End If
                        End If
                   Catch ex As Exception
                        Throw New Exception(ex.Message)
                   End Try
              End If
              Return DBNamesCollection
         End Function


    End Class

     

    Imports Microsoft.Win32
    Imports System.Text.RegularExpressions

     

    Public Class TNSNamesReader

         Private strOracleHome As String = ""
         Private strTNSNAMESORAFilePath As String = ""


         Private Function GetOracleHome() As String
              Dim rgkLM As RegistryKey = Registry.LocalMachine
              Dim rgkAllHome As RegistryKey = rgkLM.OpenSubKey("SOFTWARE\ORACLE\ALL_HOMES")

              If Not rgkAllHome.Equals("") Then
                   Dim strLastHome = ""
                   Dim objLastHome As Object = rgkAllHome.GetValue("LAST_HOME")
                   strLastHome = objLastHome.ToString()
                   Dim rgkActualHome As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\ORACLE\HOME" + strLastHome)
                   Dim strOraHome As String = ""
                   Dim objOraHome As Object = rgkActualHome.GetValue("ORACLE_HOME")
                   strOraHome = objOraHome.ToString()
                   strOracleHome = strOraHome
                   Return strOraHome
              Else
                   Return ""
              End If
         End Function

         Private Function GetTNSNAMESORAFilePath() As String
              If Not Me.GetOracleHome.Equals("") Then
                   strTNSNAMESORAFilePath = strOracleHome + "\NETWORK\ADMIN\TNSNAMES.ORA"
                   If Not (System.IO.File.Exists(strTNSNAMESORAFilePath)) Then
                        strTNSNAMESORAFilePath = strOracleHome + "\NET80\ADMIN\TNSNAMES.ORA"
                        Return strTNSNAMESORAFilePath
                   Else
                        Return strTNSNAMESORAFilePath
                   End If
              Else
                   Return ""
              End If
         End Function


         Public Function LoadTNSNames() As Collection
              Dim DBNamesCollection As New Collection
              Dim RegExPattern As String = "[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*=[\s]*\("

              GetTNSNAMESORAFilePath()
              'Adicionando una línea en blanco a la collection
              DBNamesCollection.Add("")
              If Not strTNSNAMESORAFilePath.Equals("") Then
                   Try
                        '//check out that file does physically exists
                        Dim fiTNS As New System.IO.FileInfo(strTNSNAMESORAFilePath)
                        If (fiTNS.Exists) Then
                             If (fiTNS.Length > 0) Then
                                  '//read tnsnames.ora file
                                  Dim iCount As Integer
                                  Try
                                       For iCount = 0 To Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Count - 1
                                            DBNamesCollection.Add(Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.Substring(0, Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.IndexOf(" ")))
                                       Next
                                  Catch ex As Exception
                                       Throw New Exception(ex.Message)
                                  End Try
                             End If
                        End If
                   Catch ex As Exception
                        Throw New Exception(ex.Message)
                   End Try
              End If
              Return DBNamesCollection
         End Function


    End Class

    Friday, February 22, 2008 10:40 PM

All replies

  • Hello,

     

    i am also working on an application with oracle and facing the same problem. i have another solution working with oracle odp.net and the getfactory. this works, but the problem is that you've got to install odp.net on every machine which wants to work with it. here is a sample code if you would like to try it out:

     

    Imports System.Data.Common

    Public Class Form3

     

        Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

            Dim ProviderName As String = _

                   "Oracle.DataAccess.Client"

            Dim fctry As DbProviderFactory = _

                   DbProviderFactories.GetFactory(ProviderName)

            If (fctry.CanCreateDataSourceEnumerator) Then

                Dim dsenum As DbDataSourceEnumerator = _

                           fctry.CreateDataSourceEnumerator()

                Dim dt As DataTable = dsenum.GetDataSources()

                Me.DataGridView1.DataSource = dt

            Else

                MessageBox.Show("No datasources found")

            End If

        End Sub

     

    steps to take:

     

    1: download odp.net from oracle (it's free)

    2: make new project

    3: for example what i used was a button + grid

    4: add referance oracle.dataaccess.client

    5: copy / paste code above and the grid get's filled with the datasources.

     

    p.s take care of the tnsnames.ora (you've got to put this in your odp.net home \network\admin)

     

    i hope this was helpfull for you.

     

    If you find a solution for your actual problem please let me know. because i find that a better solution rather than installing an extra component.

     

    kind regards,

     

    bryan

     

    Thursday, February 21, 2008 4:55 PM
  •  

    Hi Bryan.

    I founded a very good information at this site: http://dotnetjunkies.com/WebLog/debasish/archive/2005/11/13.aspx

    And I founded some other good information in here: http://visualbasic.ittoolbox.com/groups/technical-functional/vb-dotnet-l/parsing-an-oracle-tnsnamesora-file-526733

     

    Then, I took the from both with some little changes and I did this, I hope this help you.

    Notice that I made some changes to the RegExPattern in the public function Public Function LoadTNSNames to fit my tnsnames.ora.

    I have a form with a combobox and a button.

    In the click event from the button I added this code to test the class.

     

     

    Public Class Form1 ...

     

           Public sub button1_Click .....

                   Me.combobox1.datasource = New TNSNamesReader().LoadTNSNames()

          End Sub

     

    End class

     

    I hope this works for you.

    Best Regards,

     

    Douglas.

     

     

    Be cImports Microsoft.Win32
    Imports System.Text.RegularExpressions

     

    Public Class TNSNamesReader

         Private strOracleHome As String = ""
         Private strTNSNAMESORAFilePath As String = ""


         Private Function GetOracleHome() As String
              Dim rgkLM As RegistryKey = Registry.LocalMachine
              Dim rgkAllHome As RegistryKey = rgkLM.OpenSubKey("SOFTWARE\ORACLE\ALL_HOMES")

              If Not rgkAllHome.Equals("") Then
                   Dim strLastHome = ""
                   Dim objLastHome As Object = rgkAllHome.GetValue("LAST_HOME")
                   strLastHome = objLastHome.ToString()
                   Dim rgkActualHome As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\ORACLE\HOME" + strLastHome)
                   Dim strOraHome As String = ""
                   Dim objOraHome As Object = rgkActualHome.GetValue("ORACLE_HOME")
                   strOraHome = objOraHome.ToString()
                   strOracleHome = strOraHome
                   Return strOraHome
              Else
                   Return ""
              End If
         End Function

         Private Function GetTNSNAMESORAFilePath() As String
              If Not Me.GetOracleHome.Equals("") Then
                   strTNSNAMESORAFilePath = strOracleHome + "\NETWORK\ADMIN\TNSNAMES.ORA"
                   If Not (System.IO.File.Exists(strTNSNAMESORAFilePath)) Then
                        strTNSNAMESORAFilePath = strOracleHome + "\NET80\ADMIN\TNSNAMES.ORA"
                        Return strTNSNAMESORAFilePath
                   Else
                        Return strTNSNAMESORAFilePath
                   End If
              Else
                   Return ""
              End If
         End Function


         Public Function LoadTNSNames() As Collection
              Dim DBNamesCollection As New Collection
              Dim RegExPattern As String = "[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*=[\s]*\("

              GetTNSNAMESORAFilePath()
              'Adicionando una línea en blanco a la collection
              DBNamesCollection.Add("")
              If Not strTNSNAMESORAFilePath.Equals("") Then
                   Try
                        '//check out that file does physically exists
                        Dim fiTNS As New System.IO.FileInfo(strTNSNAMESORAFilePath)
                        If (fiTNS.Exists) Then
                             If (fiTNS.Length > 0) Then
                                  '//read tnsnames.ora file
                                  Dim iCount As Integer
                                  Try
                                       For iCount = 0 To Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Count - 1
                                            DBNamesCollection.Add(Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.Substring(0, Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.IndexOf(" ")))
                                       Next
                                  Catch ex As Exception
                                       Throw New Exception(ex.Message)
                                  End Try
                             End If
                        End If
                   Catch ex As Exception
                        Throw New Exception(ex.Message)
                   End Try
              End If
              Return DBNamesCollection
         End Function


    End Class

     

    Imports Microsoft.Win32
    Imports System.Text.RegularExpressions

     

    Public Class TNSNamesReader

         Private strOracleHome As String = ""
         Private strTNSNAMESORAFilePath As String = ""


         Private Function GetOracleHome() As String
              Dim rgkLM As RegistryKey = Registry.LocalMachine
              Dim rgkAllHome As RegistryKey = rgkLM.OpenSubKey("SOFTWARE\ORACLE\ALL_HOMES")

              If Not rgkAllHome.Equals("") Then
                   Dim strLastHome = ""
                   Dim objLastHome As Object = rgkAllHome.GetValue("LAST_HOME")
                   strLastHome = objLastHome.ToString()
                   Dim rgkActualHome As RegistryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\ORACLE\HOME" + strLastHome)
                   Dim strOraHome As String = ""
                   Dim objOraHome As Object = rgkActualHome.GetValue("ORACLE_HOME")
                   strOraHome = objOraHome.ToString()
                   strOracleHome = strOraHome
                   Return strOraHome
              Else
                   Return ""
              End If
         End Function

         Private Function GetTNSNAMESORAFilePath() As String
              If Not Me.GetOracleHome.Equals("") Then
                   strTNSNAMESORAFilePath = strOracleHome + "\NETWORK\ADMIN\TNSNAMES.ORA"
                   If Not (System.IO.File.Exists(strTNSNAMESORAFilePath)) Then
                        strTNSNAMESORAFilePath = strOracleHome + "\NET80\ADMIN\TNSNAMES.ORA"
                        Return strTNSNAMESORAFilePath
                   Else
                        Return strTNSNAMESORAFilePath
                   End If
              Else
                   Return ""
              End If
         End Function


         Public Function LoadTNSNames() As Collection
              Dim DBNamesCollection As New Collection
              Dim RegExPattern As String = "[\n][\s]*[^\(][a-zA-Z0-9_.]+[\s]*=[\s]*\("

              GetTNSNAMESORAFilePath()
              'Adicionando una línea en blanco a la collection
              DBNamesCollection.Add("")
              If Not strTNSNAMESORAFilePath.Equals("") Then
                   Try
                        '//check out that file does physically exists
                        Dim fiTNS As New System.IO.FileInfo(strTNSNAMESORAFilePath)
                        If (fiTNS.Exists) Then
                             If (fiTNS.Length > 0) Then
                                  '//read tnsnames.ora file
                                  Dim iCount As Integer
                                  Try
                                       For iCount = 0 To Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Count - 1
                                            DBNamesCollection.Add(Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.Substring(0, Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.IndexOf(" ")))
                                       Next
                                  Catch ex As Exception
                                       Throw New Exception(ex.Message)
                                  End Try
                             End If
                        End If
                   Catch ex As Exception
                        Throw New Exception(ex.Message)
                   End Try
              End If
              Return DBNamesCollection
         End Function


    End Class

    Friday, February 22, 2008 10:40 PM
  • Hi Douglas,

     

    You don't know how much you've helped me with this man. this is great it worked right away. thank you very much.

     

    best regards,

     

    bryan

    b.vanritter@erasmusmc.nl

     

    Saturday, February 23, 2008 9:56 PM
  • Hi Douglas,

     

    you know this was my initial intend also, building a login form with databases and stuff. I'm a newbee in programming, originally i'am an oracle dba for 6 years now. i couldn't get the login form to work because i wanted to get the users login to the database and if they didn't have that specific role within the database the login was denied, still can't that to work. so i moved on. posted several times on numureous sights an no one had the answer, maybe because you are also working with oracle you could help me out with this one.

     

    best regards,

     

    bryan

     

     

    Saturday, February 23, 2008 10:01 PM
  • I've attempted to use this code but it highlights the registryKey line (if not rgkAllHome.equals "" then) and says Object reference not set to an instance of an object.

    Can anyone tell me how to get around this? Really all I'm trying to do is read the datasource information from my .ora file.
    Friday, October 17, 2008 5:24 PM
  • Hi,
    Maybe You should check if there is a problem with the path you're setting to the variable
    rgkAllHome.
    Check if the path exists.

    Good Luck!

    Monday, October 27, 2008 4:37 PM
  • Hi,
    Me too got the same error, I hope error coming from  this line "

    rgkLM.OpenSubKey(

    "SOFTWARE\ORACLE\  " ALL_HOMES "  ") " here we need to give exact name (chk registry enty) instead of ALL_Homes.

    All the best

    Guna

    Wednesday, October 7, 2009 6:59 AM
  • Hello,

    I found that RegExp didn't quite work, it didn't return correct name if it was in the first line. Below is simple correction which returns also name defined in the first line of tnsnames.ora.

    Dim RegExPattern As String = "(([\n][\s]*)|^)[a-zA-Z0-9_.]+[\s]*=[\s]*\("

    And I'm using environment-variables for looking the ORACLE_HOME and TNS_ADMIN directories:

     

    Imports System.Environment
    
      Private Function GetOracleHome() As String
    
        strOracleHome = Environment.GetEnvironmentVariable("ORACLE_HOME")
        Return strOracleHome
    
      End Function
    
      Private Function GetTNSAdmin() As String
    
        strTNSAdmin = Environment.GetEnvironmentVariable("TNS_ADMIN")
        Return strTNSAdmin
    
      End Function

    Thanks for that initial code, it was a great starting point.

     

    Henkka

    Thursday, May 6, 2010 6:07 AM
  • Hi All

    I used this last example , making use of the env variables, but it doesn't populate my combobox with the values from my tnsnames.ora file which is in the TNS_ADMIN directory. Any ideas where I'm going wrong here ? I also don't get any errors:

    ######################

    Public Class TNSNamesReader

        Private strOracleHome As String = ""
        Private strTNSAdmin As String = ""


         Private Function GetOracleHome() As String

            strOracleHome = Environment.GetEnvironmentVariable("ORACLE_HOME")
            Return strOracleHome

        End Function

       Private Function GetTNSAdmin() As String

            strTNSAdmin = Environment.GetEnvironmentVariable("TNS_ADMIN")
            Return strTNSAdmin

        End Function


        Public Function LoadTNSNames() As Collection
            Dim DBNamesCollection As New Collection
            Dim RegExPattern As String = "(([\n][\s]*)|^)[a-zA-Z0-9_.]+[\s]*=[\s]*\("

            GetTNSAdmin()
            'Adicionando una línea en blanco a la collection
            DBNamesCollection.Add("")
            If Not strTNSAdmin.Equals("") Then
                Try
                    '//check out that file does physically exists
                    Dim fiTNS As New System.IO.FileInfo(strTNSAdmin)
                    If (fiTNS.Exists) Then
                        If (fiTNS.Length > 0) Then
                            '//read tnsnames.ora file
                            Dim iCount As Integer
                            Try
                                For iCount = 0 To Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Count - 1
                                    DBNamesCollection.Add(Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.Substring(0, Regex.Matches(My.Computer.FileSystem.ReadAllText(fiTNS.FullName), RegExPattern).Item(iCount).Value.Trim.IndexOf(" ")))
                                Next
                            Catch ex As Exception
                                Throw New Exception(ex.Message)
                            End Try
                        End If
                    End If
                Catch ex As Exception
                    Throw New Exception(ex.Message)
                End Try
            End If
            Return DBNamesCollection
        End Function


    End Class

    ##############

    I have a form, which loads on startup with username/password/combobox , of which the combobox must populate with all the values returned by the the above TNSLoader

    Private Sub DatabaseLogin_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.DatabaseDropdownBox.DataSource = New TNSNamesReader().LoadTNSNames()
        End Sub

    Any ideas appreciated
    Lodewicus Maas

    Sunday, March 30, 2014 7:50 PM