Related Articles

Insurance Premium Calculator – WordPress

2 weeks ago

Azure Data Engineering Core Concepts

March 17, 2024

Introduction to Data Engineering on Azure

March 3, 2024

Connect Oracle Database in SSIS Package

Creating an Oracle “ora” file is a useful approach to manage database connection details for Oracle databases. This file simplifies the connection process by allowing users to reference a user-friendly alias, known as the Net Service Name, instead of directly specifying the connection details each time. In this guide, we will explain how to create an “ora” file step-by-step, providing a detailed overview of the necessary configuration.

Creating TNSNAMES.ORA file

1. Begin by opening a text editor, such as Notepad or any other preferred text editing software.
2. Create a new, blank file to start the “ora” file.
3. The first step is to specify the Net Service Name, which acts as an alias for the database connection. It enables users to refer to the database using a more recognizable name instead of the actual connection details. The format for specifying the Net Service Name is as follows:makefileCopy codeMYDB_CONNECTION = Replace “MYDB_CONNECTION” with the desired name for your database connection.
4. Following the Net Service Name, you need to provide the connection details for the Oracle database. These details consist of several parameters that allow the application to connect to the database. The primary parameters to include are:
   • HOST: Specify the hostname or IP address of the Oracle database server.PORT: Specify the port number where the Oracle database is listening.SERVICE_NAME: Specify the service name or SID (System Identifier) of the Oracle database.USERID: Specify the username and password separated by a forward slash (/).
   Here is an example of including these parameters in the “ora” MYDB_CONNECTION = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mydatabase) ) ) In this example, replace "myhost" with the actual hostname or IP address of the Oracle database server, "1521" with the appropriate port number, and “mydatabase” with the correct service name or SID.
5. Save the file with a “.ora” extension. For instance, you can name it “mydb_connection.ora”.
6. Optionally, you can include additional configuration parameters in the “ora” file to customize the connection behavior according to your requirements. These parameters might include connection timeout settings, encryption preferences, or other connection-specific configurations.
7. Once the “ora” file is saved, you can utilize it to establish a connection to the Oracle database. Simply reference the Net Service Name specified in the file. This connection can be made using various tools and programming languages that support Oracle database connections.

By creating an “ora” file, you simplify the management of connection details for Oracle databases. With the Net Service Name acting as an alias, you can easily refer to the database without the need to repeatedly specify the connection parameters. This approach enhances efficiency and flexibility when integrating Oracle databases into your workflows, making the connection process smoother and more manageable.

In Windows, the “tnsnames.ora” file is typically saved in the “NETWORK\ADMIN” directory within the Oracle client installation folder. The exact location may vary depending on the Oracle client version and configuration. By default, the path to the “tnsnames.ora” file is as follows:

C:\oracle\client{client_version}\NETWORK\ADMIN\tnsnames.ora

Replace “{client_version}” with the actual version of the Oracle client installed on your system. If you installed the Oracle client in a different directory or customized the installation, the path may differ accordingly.

Example for TNSNAMES.ORA file is here

tnsnames.ora

ORCLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

SALESDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = saleshost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = salesdb)
)
)

Replace connection string with your database and port in the above file.

Previous article on oracle connectivity in ssis.

Official documentation on TNSNAMES.ORA file creation from Oracle corporation.