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 ));