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

Array (PL/SQL Table) to String Conversion Utility

Introduction

This package consists of reusable functions, which will provide the facility to convert from delimited string to array (PL/SQL tables) and from array (PL/SQL tables) to delimited string.

For example: The comma separated string, ‘Red,Blue,Yellow’, needs to be converted into an array namely x. After conversion, the length of array x will be 3 and the value of the array x will be as follows:

x(1) = ‘Red’
x(2) = ‘Blue’
x(3) = ‘Yellow’

Usage

This package consists of the following three functions:

First function namely fn_string_to_varchararray, requires two parameters. The first parameter is a varchar2 data type, which needs to be converted into an array (PL/SQL tables). The second parameter is an optional varchar2 data type parameter and the default value is comma, which gives the flexibility to use any delimiter.

Second function namely fn_varchararray_to_string, requires three parameters. The first parameter is an array of tab_varchar2_array type, which needs to be converted into a string. The second parameter is an optional varchar2 data type parameter and the default value is NULL. The third parameter is an optional varchar2 data type parameter and the default value is comma, which gives the flexibility to use any delimiter.

Third function namely fn_numberarray_to_string, requires three parameters. The first parameter is an array of tab_varchar2_array type, which needs to be converting into a string. The second parameter is an optional varchar2 data type parameter and the default value is NULL. The third parameter is an optional varchar2 data type parameter and the default value is comma, which gives the flexibility to use any delimiter.

Requirement
Create the following Types:

CREATE TYPE TAB_VARCHAR2_ARRAY AS TABLE OF VARCHAR2 (4000);
CREATE TYPE TAB_NUMBER_ARRAY AS TABLE OF NUMBER;

Create the following Package:

CREATE OR REPLACE PACKAGE pkg_array_utility
IS
  /*************************************************************************************************
  Purpose :- This package contains function and procedure to manipulate Array(PL/SQL Tables).
  Author  :- Sachin Chauhan
  Creation Date :- 26-DEC-2007
  Change Summary: -
  --------------------------------------------------------------------------------------------------
  S. No.  Date      Author                              Description
  --------------------------------------------------------------------------------------------------
  1.
  2.
  *************************************************************************************************/
  FUNCTION fn_string_to_varchararray (
    i_sv_list        IN   VARCHAR2,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN tab_varchar2_array;

  FUNCTION fn_varchararray_to_string (
    i_var_array      IN   tab_varchar2_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2;

  FUNCTION fn_numberarray_to_string (
    i_var_array      IN   tab_number_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_array_utility
IS
  FUNCTION fn_string_to_varchararray (
    i_sv_list        IN   VARCHAR2,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN tab_varchar2_array
  IS
    /***********************************************************************************************
    Purpose :- This function is used for converting string into array (pl/sql table).
    Author  :- Sachin Chauhan
    Creation Date :- 26-DEC-2007
    Change Summary: -
    ------------------------------------------------------------------------------------------------
    S. No.  Date      Author                              Description
    ------------------------------------------------------------------------------------------------
    1.
    2.
    ***********************************************************************************************/
    l_pls_idx        PLS_INTEGER;
    l_sv_list        VARCHAR2 (32767)          := i_sv_list;
    l_tab_splittbl   tab_varchar2_array := tab_varchar2_array ();
    l_pls_del_len    PLS_INTEGER               := LENGTH (i_sv_delimiter);
  BEGIN
    IF l_sv_list IS NOT NULL THEN
      LOOP
        -- search for delimiter string
        l_pls_idx := INSTR (l_sv_list, i_sv_delimiter);
        -- increase the size of array
        l_tab_splittbl.EXTEND;

        -- check last search of delimiter is success
        IF l_pls_idx = 0 THEN
          l_tab_splittbl (l_tab_splittbl.COUNT) := SUBSTR (l_sv_list, 1);
        ELSE
          l_tab_splittbl (l_tab_splittbl.COUNT) := SUBSTR (l_sv_list, 1, l_pls_idx - 1);
        END IF;

        -- exit from loop when last string
        EXIT WHEN NVL (l_pls_idx, 0) = 0;
        l_sv_list := SUBSTR (l_sv_list, l_pls_idx + l_pls_del_len);
      END LOOP;
    END IF;

    RETURN l_tab_splittbl;
  END fn_string_to_varchararray;

  FUNCTION fn_varchararray_to_string (
    i_var_array      IN   tab_varchar2_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2
  IS
    /***********************************************************************************************
    Purpose: - This function is used to convert array of varchar2 to string.
    Author  :- Sachin Chauhan
    Creation Date :- 26-DEC-2007
    Change Summary:-
    ------------------------------------------------------------------------------------------------
    S. No.  Date      Author                              Description
    ------------------------------------------------------------------------------------------------
    1.
    2.
    ***********************************************************************************************/
    l_sv_string   VARCHAR2 (32767);
    l_sv_delim    VARCHAR2 (100);
  BEGIN
    IF i_var_array.COUNT > 0 THEN
      FOR i IN i_var_array.FIRST .. i_var_array.LAST
      LOOP
        -- to avoide delimiter after last string
        IF i = 2 THEN
          l_sv_delim := i_sv_delimiter;
        END IF;

        l_sv_string :=
             l_sv_string
          || l_sv_delim
          || CASE
               -- add 'NULL' as string instead of null
             WHEN i_var_array (i) IS NULL THEN 'NULL'
               ELSE i_sv_enclose || i_var_array (i) || i_sv_enclose
             END;
      END LOOP;
    END IF;

    RETURN (l_sv_string);
  END;

  FUNCTION fn_numberarray_to_string (
    i_var_array      IN   tab_number_array,
    i_sv_enclose     IN   VARCHAR2 DEFAULT NULL,
    i_sv_delimiter   IN   VARCHAR2 DEFAULT ','
  )
    RETURN VARCHAR2
  IS
    /***********************************************************************************************
    Copyright      : Copyright © P&G 

    Purpose: - This function is used to convert vararray of number to string.
    Author  :- Sachin Chauhan
    Creation Date :- 26-DEC-2007
    Change Summary:-
    ------------------------------------------------------------------------------------------------
    S. No.  Date      Author                              Description
    ------------------------------------------------------------------------------------------------
    1.
    2.
    ***********************************************************************************************/
    l_sv_string   VARCHAR2 (32767);
    l_sv_delim    VARCHAR2 (100);
  BEGIN
    IF i_var_array.COUNT > 0 THEN
      FOR i IN i_var_array.FIRST .. i_var_array.LAST
      LOOP
        -- to avoide delimiter after last string
        IF i = 2 THEN
          l_sv_delim := i_sv_delimiter;
        END IF;

        l_sv_string :=
             l_sv_string
          || l_sv_delim
          || CASE
               -- add 'NULL' as string instead of null
             WHEN i_var_array (i) IS NULL THEN 'NULL'
               ELSE i_sv_enclose || TO_CHAR (i_var_array (i)) || i_sv_enclose
             END;
      END LOOP;
    END IF;

    RETURN (l_sv_string);
  END;
END;
/

Examples

1. Converting from String to Array (PL/SQL Tables) in PL/SQL block

DECLARE
  l_sv_string   VARCHAR2 (50) := 'Red,Blue,Yellow';
  l_sv_arr      tab_varchar2_array := tab_varchar2_array ();
BEGIN
  l_sv_arr := pkg_array_utility.FN_STRING_TO_VARCHARARRAY
                                                         (l_sv_string);
  DBMS_OUTPUT.put_line (l_sv_arr(1));
  DBMS_OUTPUT.put_line (l_sv_arr(2));
  DBMS_OUTPUT.put_line (l_sv_arr(3));
END;
The Result of PL/SQL block will be:

Red
Blue
Yellow

2. Converting from Varchar2 Array (PL/SQL Tables) to String in PL/SQL block

DECLARE
  l_sv_arr      tab_varchar2_array := tab_varchar2_array ();
  l_sv_string   VARCHAR2 (50);
BEGIN
  l_sv_arr.EXTEND;
  l_sv_arr (1) := 'Red';
  l_sv_arr.EXTEND;
  l_sv_arr (2) := 'Blue';
  l_sv_arr.EXTEND;
  l_sv_arr (3) := 'Yellow';
  l_sv_string := pkg_array_utility.fn_varchararray_to_string (l_sv_arr);
  DBMS_OUTPUT.put_line (l_sv_string);
  l_sv_string := pkg_array_utility.fn_varchararray_to_string (l_sv_arr, '''');
  DBMS_OUTPUT.put_line (l_sv_string);
  l_sv_string := pkg_array_utility.fn_varchararray_to_string (l_sv_arr, '''', '^');
  DBMS_OUTPUT.put_line (l_sv_string);
END;

The Result of PL/SQL block will be:

Red,Blue,Yellow
'Red','Blue','Yellow'
'Red'^'Blue'^'Yellow'

3. Converting from Varchar2 Array (PL/SQL Tables) to String in SQL

SELECT pkg_array_utility.fn_varchararray_to_string
          (CAST (MULTISET (SELECT   column_name
                               FROM dba_tab_columns
                              WHERE owner = 'SCOTT' AND table_name = 'EMP'
                           ORDER BY column_id) AS tab_varchar2_array))
  FROM DUAL;

The Result of SQL will be:

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

Note: Example number 2 and 3 can be used for number data type using the fn_numberarray_to_string function.

Convert Numbers into Words using PL/SQL

Introduction

This function will provide the facility to convert a number to words. This function can accept value one to duodecillion. It also accept decimal value, if the decimal value is more than 2 digit then it will use upto 2 decimal only.

This is a improved version of Tom Kyte solution.

Create below procedure:

CREATE OR REPLACE function spell_number( p_number in number)
return varchar2
as
  type myArray is table of varchar2(20);
  l_str    myArray := myArray( '',
                         ' thousand ', ' million ',
                         ' billion ', ' trillion ',
                         ' quadrillion ', ' quintillion ',
                         ' sextillion ', ' septillion ',
                         ' octillion ', ' nonillion ',
                         ' decillion ', ' undecillion ',
                         ' duodecillion ' );

  l_num varchar2(50) default trunc( p_number );
  l_dec varchar2(50);
  l_return varchar2(4000);
begin
  for i in 1 .. l_str.count loop
    exit when l_num is null;
    if ( substr(l_num, length(l_num)-2, 3) <> 0 ) then
      l_return := to_char(
                    to_date(
                      substr(l_num, length(l_num)-2, 3),
                            'J' ),
                     'Jsp' ) || l_str(i) || l_return;
    end if;
    l_num := substr( l_num, 1, length(l_num)-3 );
  end loop;
  if instr(p_number, '.') > 0 then
    --l_dec := substr(p_number, instr(p_number, '.')+1);
    l_dec := substr(ltrim(to_char(p_number-trunc(p_number), '.99')), 2);
    --dbms_output.put_line(l_dec);
    l_return := ltrim(rtrim(l_return)) || ' and ' || ltrim(rtrim(spell_number(l_dec)));
  end if;
  return replace(l_return, '-', ' ');
end;
/

Usage

SQL> select spell_number(1342344322332) from dual;

SPELL_NUMBER(1342344322332)
--------------------------------------------------------------------------------
One trillion Three Hundred Forty Two billion Three Hundred Forty Four million Th
ree Hundred Twenty Two thousand Three Hundred Thirty Two