Samoratech.com

`How to read “tnsnames.ora” file with the help of external table?`

By Sameer Wadhwa (Oracle Techniques)

External tables are very powerful feature introduced in Oracle 9i. With the help of this feature you can read data stored outside the Oracle database.

To perform this, first you need to create a directory and let the Oracle know the location of file which you want to read. In my case, I am reading tnsnames.ora file stored in $ORACLE_HOME/network/admin. So, I am creating a directory in oracle database as follows:-

Sql> sqlplus /nolog

Sql> connect sys/**** as sysdba

SQL> create directory tnsdir as 'E:\oracle\ora92\network\admin';

Directory created.

SQL> col owner format a5

SQL> col directory_name format a15

SQL> col directory_path format a30

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

----- --------------- ------------------------------

SYS TNSDIR E:\oracle\ora92\network\admins

Now you need to create a table in the Oracle Database to query and read the external data. To read the tnsnames.ora file, I am creating the following table as

create table readtnsora

(

connectstring varchar2(50)

)

organization external

(

type oracle_loader

default directory tnsdir

access parameters

(

records delimited by newline

load when (1:1) != ' '

fields terminated by '='

(

connectstring char(500)

)

location ('tnsnames.ora')

)

reject limit 1000

Here you are noticing completely different syntax as compared to creation of regular table. This syntax is for external table. The most of syntax is similar to control file in sql*loader utility. If you have a good knowledge of SQL*LOADER, you may get a full advantage of external tables.

Once you created the external table as above, you are ready for reading the data from the external file.

The following query is actually reading the connectstring from the tnsnames.ora file.

SQL> select * from readtnsora;

CONNECTSTRING

--------------------------------------------------

# Generated by Oracle configuration tools.

SAMOR92

myor92

INST1_HTTP

EXTPROC_CONNECTION_DATA

SAMOR921

6 rows selected.

Here is my sample tnsnames.ora file.

# TNSNAMES.ORA Network Configuration File: E:\oracle\ora92\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

SAMOR92 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = samoracle)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SAMOR92)

)

myor92 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = samoracle)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SAMOR92)

)

INST1_HTTP =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = samoracle)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = SHARED)

(SERVICE_NAME = MODOSE)

(PRESENTATION = http://HRService)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

SAMOR921 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = samoracle)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SAMOR92)

)

In short, external tables are based upon the data outside the database. They are read only and NO DML (Insert/update/delete) operations are allowed and no indexes can be created.

More Information about External tables can be found on OTN site at

http://otn.oracle.com/products/oracle9i/daily/sept19.html

Hope the above information will help …

-- Author

Sameer Wadhwa

Disclaimer:

The author does not guarantee that this information is error-free.

If any errors are found, please report them to author at SamOracle@Yahoo.com

Ask and Solve Database Problems at OraTechSupportGroup

What do you think about Oracle Techniques?