Creating ODBC Connections for Oracle

Before making the ODBC connection to Oracle databases, you must set up a configuration file (tnsnames.ora) that specifies the connection details of the database to which you want to connect.

If you do not use this file, you must specify the connection details (server name, port number, database name) every time you connect to the database instead of merely specifying the alias name. Place this file in the Oracle directory (for example, C:\oracle\product\12.1.0\client_1\NETWORK\ADMIN) and add a new environment variable TNS_ADMIN (like the Path variable) to direct the system where to look for the file.

  1. Launch the ODBC Data Source Administrator.
  2. On the System DSN tab, click Add. The Create New Data Source dialog box appears.
  3. From the list shown in the dialog box, choose Oracle in OraClient12Home1_32bit.
  4. Click Finish. The Oracle ODBC Driver Configuration dialog box appears.
  5. Fill in the following fields as appropriate:

    • Data Source Name field: Type the data source name.

    • Description: Type a brief description of the database.
    • TNS Service Name: Select the name of the Oracle server.
    • User ID: Type the name of the database.
  6. On the Application tab, select the appropriate options as required.
  7. Optional: To test the connection to the database, click Test Connection. The Oracle ODBC Driver Connect dialog box appears.
  8. Optional: Type a password and click OK.
  9. If the connection is successful, a message box indicating a successful connection appears. Otherwise, an error message appears advising you that the system is unable to connect. Click OK.

    If there is an error, resolve the problems indicated in the error message, for example, invalid user name.

  10. Click OK.
  11. In the ODBC Data Source Administrator dialog box, click OK to complete the connection.
    For more detailed information on the ODBC, please refer to the appropriate third party documentation.