Exctract TNS Names from TNSNAMES.ORA using Visual Basic 6 (VB6)

To extract TNS Names from tnsnames.ora file you can use below listed function Read_Tns_File.
This function extract TNS Names and add them into ComboBox(cmbTns).

Private Sub Read_Tns_File(strFileName As String, cmbTns As ComboBox)
    On Error GoTo FileErrHandler ' Jump if an error occurs
    Dim intTnsFile As Integer
    Dim strData As String
    Dim bolTnsStart As Boolean
    Dim bolBracketStart As Boolean
    Dim intBracketCounter As Integer
    intBracketCounter = 0
    intTnsFile = FreeFile
    ' open tns file
    Open strFileName For Input As intTnsFile
    ' read tns file
    Do Until (EOF(intTnsFile))
        Line Input #intTnsFile, strData
        ' checking for remarks
        If Left(LTrim(strData), 1) <> "#" And LTrim(strData) <> "" Then
        If Not bolTnsStart Then
            Dim intEqualPos As Integer
            Dim intTnsStringSingle As String
            
            intEqualPos = InStr(strData, "=")
            
            If intEqualPos = 0 Then
              intTnsStringSingle = strData
            Else
              intTnsStringSingle = Mid(strData, 1, intEqualPos - 1)
            End If

            cmbTns.AddItem (Trim(intTnsStringSingle))
            bolTnsStart = True
        End If

        If bolTnsStart And InStr(strData, "(") > 0 Then
          bolBracketStart = True
        End If

        intBracketCounter = intBracketCounter + (Len(strData) - Len(Replace(strData, "(", "")))
        intBracketCounter = intBracketCounter - (Len(strData) - Len(Replace(strData, ")", "")))
      End If

      If intBracketCounter = 0 And bolBracketStart Then
        bolBracketStart = False
        bolTnsStart = False
      End If
    Loop
    Close intTnsFile
    Exit Sub
FileErrHandler:
    ' if any error occurs while file handling exit the procedure
    Close intTnsFile
    Exit Sub
End Sub

Get TNS Names from tnsnames.ora file

To extract TNS Names from tnsnames.ora file you can use below listed function GET_TNS_NAMES.

If you are using Visual Basic then goto http://sach1607.blogspot.com/2008/02/exctract-tns-names-from-tnsnamesora.html

First create directory and PL/SQL table:

create directory TNS_DIR as 'C:\oracle\ora92\network\ADMIN\';
grant read, write on directory TNS_DIR to public;

create type tab_varchar2_array as table of varchar2 (4000);

Now create function which read TNS Names from tnsnames.ora file and convert them into records using pipelined.


CREATE OR REPLACE FUNCTION get_tns_names
  RETURN tab_varchar2_array PIPELINED
IS
  l_sv_tns_file        VARCHAR2 (100)     := 'tnsnames.ora';
  l_rc_file_id         UTL_FILE.file_type;
  l_sv_line            VARCHAR2 (32767);
  l_bo_tns_start       BOOLEAN            := FALSE;
  l_bo_bracket_start   BOOLEAN            := FALSE;
  l_pls_bracket_ctr    PLS_INTEGER        := 0;
BEGIN
  l_rc_file_id := UTL_FILE.fopen ('TNS_DIR', l_sv_tns_file, 'r', max_linesize => 32767);

  LOOP
    BEGIN
      UTL_FILE.get_line (l_rc_file_id, l_sv_line);

      -- checking for remarks
      IF SUBSTR (LTRIM (l_sv_line), 1, 1) != '#' AND LTRIM (l_sv_line) IS NOT NULL THEN
        IF NOT l_bo_tns_start THEN
          DECLARE
            l_pls_equal_pos          PLS_INTEGER;
            l_sv_tns_string_single   VARCHAR2 (100);
          BEGIN
            l_pls_equal_pos := INSTR (l_sv_line, '=');

            IF l_pls_equal_pos = 0 THEN
              l_sv_tns_string_single := l_sv_line;
            ELSE
              l_sv_tns_string_single := SUBSTR (l_sv_line, 1, l_pls_equal_pos - 1);
            END IF;

            PIPE ROW (TRIM (l_sv_tns_string_single));
            l_bo_tns_start := TRUE;
          END;
        END IF;

        IF l_bo_tns_start AND INSTR (l_sv_line, '(') > 0 THEN
          l_bo_bracket_start := TRUE;
        END IF;

        l_pls_bracket_ctr :=
           l_pls_bracket_ctr
           + (LENGTH (l_sv_line) - LENGTH (REPLACE (l_sv_line, '(', '')));
        l_pls_bracket_ctr :=
           l_pls_bracket_ctr
           - (LENGTH (l_sv_line) - LENGTH (REPLACE (l_sv_line, ')', '')));
      END IF;

      IF l_pls_bracket_ctr = 0 AND l_bo_bracket_start THEN
        l_bo_bracket_start := FALSE;
        l_bo_tns_start := FALSE;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
      WHEN OTHERS THEN
        IF UTL_FILE.is_open (l_rc_file_id) THEN
          UTL_FILE.fclose (l_rc_file_id);
        END IF;
        RAISE;
    END;
  END LOOP;

  UTL_FILE.fclose (l_rc_file_id);
END get_tns_names;
/

Now fetch the TNS names use select statement:

SELECT * FROM TABLE(CAST(GET_TNS_NAMES AS TAB_VARCHAR2_ARRAY ));