Omnis Technical Note TNSQ0034 September 2014

Using Oracle Instant Client on Windows

for Omnis Studio 5/6.x
by Gary Ashford

Introduction
Previous Tech Notes (TNSQ0010 & TNSQ0010a) have described installation of Oracle Instant Client for Mac OSX. Allowing for operating system differences, the installation process for Windows is essentially the same:

  • Download the Instant Client zip archive from oracle.com
  • Extract files from the zip archive to a folder on the local filesystem
  • Modify system environment variables
  • Use a tnsnames.ora file to define one or more logon hostnames
  • Open Omnis Studio and verify connection settings

Download Instant Client
Oracle Instant Client can be downloaded from www.oracle.com. You can use any of the Instant Client packages listed for your platform provided that they support OCI applications. We recommend Instant Client Basic or Instant Client Basic Lite. You may need to create a free Oracle account if you do not already have one.

Extract files from zip Archive
Once downloaded, create a folder on your local filesystem and open the zip archive. Drag all files from the zip archive and place them into the newly created folder:

Omnis Webshop sample app in the Chrome Browser
Click to enlarge. This image depicts files extracted from the instantclient-basiclite-12.1.0.1.0.zip archive.

Modify System Environment Variables
Open the Advanced System properties window. On Windows 8,this can be achieved in Windows Explorer by right-clicking on This PC, e.g. This PC → Properties → Advanced system settings → Environment Variables...

The Environment Variables system dialog
  • Create a system variable named TNS_ADMIN. The value of this variable must point to a folder that will later contain your tnsnames.ora file. In this example, the tnsnames.ora file will be placed inside the c:\instantclient_12_1 folder along with the other files.
  • Look for the ORACLE_HOME variable. If this variable exists, it should be deleted to prevent conflict with Oracle Instant Client.
  • Look for the PATH variable. Remove any existing references to previous Oracle clientware installations- if they exist. Add an entry for the new Instant Client folder (c:\instantclient_12_1 in this example).

Configure tnsnames.ora
Create (or copy) a text file named tnsnames.ora in the location specified by TNS_ADMIN. Existing users of Oracle clientware will be familiar with the formatting requirements of the tnsnames.ora file. In summary, each logon hostname that you define should be formatted as follows:

        ORA11 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = orcl)
        )
    )

In the above example, ORA11 will be the logon hostname, and the database service name is 'orcl' (an often-used default name). The port and ip-address should point to your Oracle database server which should be configured to accept the tnsnames naming method.

Verify Connection Settings
When you open Omnis Studio, verify that the Oracle DAM has loaded by looking in the Omnis trace log. If an error indicates that the Oracle DAM could not be loaded, verify the earlier modification to the PATH environment variable is in place. (PATH needs to include the folder containing oci.dll).

Use the SQL Browser to create an Oracle session using a hostname from your tnsnames.ora file. Supply the required username and password and log on to test the connection.

If an error indicates that the logon name could not be found in the tnsnames.ora file, verify the the TNS_ADMIN variable is set correctly and the tnsnames.ora is formatted correctly- particularly in respect of tabs and carriage returns. Note that TNS_ADMIN should contain the path and folder name only (and not 'tnsnames.ora'). Also double-check that ORACLE_HOME is not set when using Oracle Instant Client.

If you are using the 64-bit edition of Omnis Studio please note that the Oracle DAM will require the 64-bit version of Oracle Instant Client. Conversely, the DAM supplied with the 32-bit edition of Omnis requires the 32-bit version of Instant Client.