Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Configure an Oracle Server SQL connection into your setup project

Tue Sep 14, 2021 4:22 pm

This solution should work for both type of Oracle Database server installation: local running Oracle Database server and remote installed Oracle Database server.

I. To test ODBC connection and run an SQL script on an Oracle server you should configured our “SQL Databases“ page like this:

image1.jpg
image1.jpg (199.05KiB)Viewed 246514 times


More exactly using a custom connection string like of this format:

Code: Select all

Driver=Oracle in instantclient_19_12;DBQ=<TNS Service Name>;Uid=<username>;Pwd=<password>

The <TNS Service Name> can be found in the tnsnames.ora file located in the OracleHome\network\admin folder on the machine where the Oracle ODBC driver is installed. For instance, here it is the content of an tnsnames.ora file:

Code: Select all

# tnsnames.ora Network Configuration File: C:\Users\Caphyon\Desktop\ztzt\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.caphyon.net)
    )
  )

ORCL_CUSTOM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-NUE74LC)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.caphyon.net)
    )
  )


The TNS Service name entries in the above files are ORACLR_CONNECTION_DATA, LISTENER_ORCL, ORCL and ORCL_CUSTOM. So, a working ODBC connection string would be:

  • Driver=Oracle in instantclient_19_12;DBQ=ORCL_CUSTOM;Uid=system;Pwd=caphyon!1A
For all supported parameters by the ODBC connection string check the help of the ODBC driver you are trying to use. E.g check the “21.4.1 Format of the Connection String” Oracle help section.

II. For troubleshooting purposes, to double check and test the ODBC connection string you can also use the “ODBC Data Source Administrator” manager of Windows OS. Just proceed like this:

1. Open “ODBC Data Source Administrator” and go to “System DSN”
image2.jpg
image2.jpg (113.44KiB)Viewed 246514 times
2. Add a new system dsn and select the Oracle driver -> Finish
image3.jpg
image3.jpg (50.1KiB)Viewed 246514 times
3. Fill in the “TNS Service Name” field with the name of the TNS Service you want to use (check the content of OracleHome\network\admin\tnsnames.ora file)

4. Use the [Test Connection] button and fill in your TNS service name, username and password -> click [OK] and you should get a “Connection successful” message
image4.jpg
image4.jpg (57.5KiB)Viewed 246514 times

III. When it comes to connecting to an Oracle server installed on a remote machine you may want to make sure that:

1. On host machine Oracle Database service is running

2. On host machine there is an Oracle listener configured to run and listen on a TCP port (e.g. default port is 1521) - for troubleshooting purposes you can check the configuration settings of OracleHome\network\admin\listener.ora file

3. On host machine there is a firewall exception inbound rule for TCP protocol on port number the above Oracle listener service is configured to run

4. There is a TCP/IP network connection between the client and host machine

5. On the client machine there is installed and configured an Oracle ODBC driver

6. On the client machine the Oracle client is configured to use a Net Service Name connections (i.e. the sqlnet.ora and tnsnames.ora files are configured in OracleHome\network\admin\ folder on client machine)
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Sample Projects”