• Introduction
• Connect to Oracle
• Setup the Oracle OCI drivers
• Create an EZCONNECT string
• Troubleshooting
• More Information

Introduction

This article explains how to connect to Oracle using Easy Connect (EZCONNECT). This is the quickest/easiest way to connect to an Oracle database. This method will work with all operating systems.

It covers the following topics:

1. How to connect to Oracle (with code sample).
2. How to setup the required Oracle OCI drivers.
3. How to create and use an EZCONNECT string

Connect to Oracle [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The EZCONNECT code sample will work with all operating systems.

Follow these steps to connect:

1. Create a new channel to try out the code:
   • Create a channel like this:
     • Name: DB Oracle Connection (or similar)
     • Source: From Translator
     • Destination: To Channel
   • Alternatively use an existing channel:

     Your channel will need a From/To Translator or a Filter component.

2. Connect to a database using Easy Connect:
   • Paste this code into the Translator:

     If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Oracle database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

     function main()
           local conn = db.connect{   
           api=db.ORACLE_OCI, 
           name='Oracle EZCONNECT string',
           user='your_login', 
           password='secret',
           use_unicode = true,
           live = true
        }
     
        conn:execute{sql='SELECT * FROM <your table>', live=true}
     end

   • The name is an Oracle EZCONNECT string.
3. Adapt the code to your requirements.

Setup the Oracle OCI drivers [top]

This procedure installs the the Oracle OCI drivers globally, which means that the drivers will be available for all users on the machine.

We recommend installing the Oracle OCI drivers (and other database drivers) in the correct/default global driver directories for your operating system. However it is possible to install drivers locally (for a single user) if you wish to restrict access.

The name of the OCI driver library depends on the operating system:

Windows:

1. Check if there is an existing tsnnames.ora file:

   If you find an existing tsnnames.ora file then you should speak to your DBA (database administrator) to identify the correct course of action.

   1. Use File Explorer to search for the tsnnames.ora file.
   2. If you do not find a tsnnames.ora file, then continue to step 2 and install the Oracle Instant Client drivers.
   3. If you find a tsnnames.ora file, something like one of these — then skip the install (step 2) and speak to your DBA to identify the correct tnsnames.ora file to use:
      
2. Install the Oracle Instant Client drivers:

   This step assumes that Oracle database and drivers are not installed on your machine. If drivers are already installed you must skip this step and talk to your DBA (database administrator) to identify the where the instantclient is installed, and where the tnsnames.ora is located.

   Warning: Do not install Instant Client drivers if Oracle and/or the Instant Client drivers are already installed!

   In this case you should speak to your DBA (database administrator) to identify the correct course of action.

   1. Download the Instant Client Downloads for Microsoft Windows (x64) 64-bit:
      1. From: https://www.oracle.com/technetwork/topics/winx64soft-089540.html
      2. Choose: The latest version of the Base package, either Basic or Basic Light.

         Choosing the latest package version ensures that it will be compatible with the latest Oracle release.

         Note: In this case we chose and older version of the OCI drivers (12.2.0.1.0) as the latest version (18.3.0.0.0) was not supported by Iguana at this time.

         Please contact us at support@interfaceware.com if you need to know the latest supported version of the OCI drivers.

   2. Install the package in the c:\Oracle directory:

      You can actually install the drivers anywhere — but we use c:\oracle as it is the “standard” location recommended by Oracle.

      1. Create the c:\Oracle directory, like this:
         
      2. Unzip the downloaded drivers into the c:\Oracle directory:
         1. Right click on the downloaded file and choose Extract All
         2. Browse to the install directory
         3. And click the Extract button:
            
      3. The drivers are extracted into a new directory in c:\Oracle that looks something like this:
         
      4. Inside this directory there is an instantclient_X_Y drivers directory, like this:
         
      5. Move this directory to c:\Oracle.
      6. Delete the empty folder the zip was originally unpacked into:
         
      7. There should now be a single folder in c:\Oracle containing the OCI drivers:
         
3. Add the OCI drivers directory to the windows Path system variable:

   This will enable Iguana to find the Oracle drivers automatically.

   1. Search for “Environment” and choose Edit the system environment variables:
      
   2. The System Properties window will open on the Advanced page:
      
   3. Click the Environment Variables button near the bottom of the page:
      
   4. Highlight the System Path variable and click Edit:
      
   5. The Edit environment variable window appears.
   6. Add the location of your oci.dll file on the next line, and click OK to save your changes:
      
4. Ensure that the TNS_NAMES environment variable points to the directory containing tnsnames.ora:

   You need to point TNS_NAMES to the directory containing tnsnames.ora — usually <instantclient install>/network/admin. If TNS_NAMES does not exist you will also need to create it.

   Warning: If the Oracle Database Server is installed on your machine you should (probably) not use/set TNS_ADMIN — discuss this with your DBA to determine the correct course of action.

   1. Search for “Environment” and choose Edit the system environment variables:
      
   2. The System Properties window will open on the Advanced page:
      
   3. Click the Environment Variables button near the bottom of the page:
      
   4. If TNS_ADMIN does not exist then click New… to create it, or click Edit if it already exists:
      
   5. Enter the path to the directory containing tnsnames.ora, and click OK:

      If you installed instantclient (step 2) then use the installation path you chose, probably C:\Oracle\instantclient_12_2\network\admin. If you are using an existing driver install then use the path that your DBA (database administrator) advises you to use.

      

5. Restart your computer so that the Environment Variables changes take effect.
6. Select the Oracle Instant Client (OCI) driver in Iguana:
   1. Open Iguana > Settings > Databases and click the Edit button for OCI Oracle:
      
   2. Select Automatically choose which OCI Oracle shared library to load, then click Save Changes:
      
   3. You will need to restart Iguana for the settings to take effect:
      
   4. Confirm that Iguana automatically finds the correct library:

      Iguana automatically finds the oci.dll file because it searches the directory that we added to the Path earlier in step (4.6).

      
      

Linux:

These instructions should work for any modern version of Linux. We install the drivers in the /usr/lib (not /usr/local/lib) for greater compatibility — as some (RHEL based) Linux versions do not check /usr/local/lib for libraries by default.

1. Check if there is an existing tsnnames.ora file:

   If you find an existing tsnnames.ora file then you should speak to your DBA (database administrator) to identify the correct course of action.

   1. Use File Explorer to search for the tsnnames.ora file. <UP TO HERE>
   2. If you do not find a tsnnames.ora file, then continue to step 2 and install the Oracle Instant Client drivers.
   3. If you find a tsnnames.ora file, something like one of these — then skip the install (step 2) and speak to your DBA to identify the correct tnsnames.ora file to use:
2. Install the Oracle Instant Client drivers:
   1. Download the Instant Client for Linux x86-64 (64-bit)
      1. From: https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
      2. Choose: The latest zip version of the Base package, either Basic or Basic Light.

         Choosing the latest package version ensures that it will be compatible with the latest Oracle release.

         Note: In this case we chose and older version of the OCI drivers (12.2.0.1.0) as the latest version (18.3.0.0.0) was not supported by Iguana at this time.

         Please contact us at support@interfaceware.com if you need to know the latest supported version of the OCI drivers.

   2. Unzip the package in the /usr/lib directory:
      1. Terminal Commands: Unzip the instant client package:
         Note: Change <version> to match the name of your download.

         cd /usr/lib
         sudo unzip ~/Downloads/instantclient-basic-linux.x64-<version>.zip

      2. Terminal Command: Confirm that the /usr/lib/instantclient_X_Y was created:

         ls -d in*

         You should see something like this:
         

      3. Terminal Command: Check the contents of the instant client directory:

         ls instantclient*

         You should see the instant client driver files like this:
         

   3. Terminal Command: Create a symbolic link in the /usr/lib directory:
      Note: replace “<_X_Y>” with the version numbers to match the directory name for the version you downloaded.

      If you are upgrading to a new version of OCI you will need to delete the old  symbolic link and recreate it to point to the new version of OCI.

      sudo ln -s /usr/lib/instantclient<_X_Y>/libclntsh.so /usr/lib/

      1. Terminal Command: Confirm that the symbolic link was created:

         ls -l libclntsh.so

         You you should see the link like this:
         

   4. Update the runtime link path for dynamic libraries:

      This tells the instantclient software where to look for dynamic library dependencies. Only do this if the instantclient is the only Oracle software you are installing! Do not do this if you are installing the Oracle database server on the same machine, instead discuss it with your DBA.

      1. Terminal Commands: Update the instant client configuration file:

         sudo sh -c "echo /opt/oracle/instantclient_18_3 > \ /etc/ld.so.conf.d/oracle-instantclient.conf"
          
         sudo ldconfig

3. Install the libaio (or libaio1) package:

   You will need to different (package installer) commands depending on the version of Linux you are using. For Ubuntu (Debian based) use apt, for CentOS (RHEL based) use yum.

   1. Ubuntu or other Debian based Linux:
      1. Terminal Command: Get the latest package info:

         sudo apt update

      2. Terminal Command: Install libaio/libaio1:

         sudo apt install libaio1

   2. CentOS or other Red Hat based Linux:
      1. Terminal Command: Get the latest package info:

         yum check-update

      2. Terminal Commands: Install libaio/libaio1:

         sudo yum install libaio

4. Delete the zip package (or keep a copy if you prefer).
5. Select the Oracle Instant Client (OCI) driver in Iguana:
   1. Open Iguana > Settings > Databases and click the Edit button for OCI Oracle:
      
   2. Select Automatically choose which OCI Oracle shared library to load, then click Save Changes:
      
   3. You will need to restart Iguana for the settings to take effect:
      
   4. Confirm that Iguana automatically finds the correct library:

      Iguana finds the symbolic link /usr/lib/libclntsh.so that we created in step (4). This is because /usr/lib is (one of the places) where the Linux automatically searches for dynamic library files.

      

Mac:

1. Download the Instant Client for macOS (Intel x86)
   1. From: https://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
   2. Choose: The latest version of the Base package, either Basic or Basic Light.

      Choosing the latest package version ensures that it will be compatible with the latest Oracle release.

2. Unzip the package in the /usr/local directory:
   1. Terminal Command: Unzip the instant client package:
      Note: Change <version> to match the name of your download.

      cd /usr/local
      sudo unzip ~/Downloads/instantclient-basic-macos.x64-<version>.zip

   2. You should see a directory of the form /usr/local/instantclient_X_Y, something like this:

      The /usr/local directory is hidden in Finder, so use Go>Go to Folder and enter “/usr/local” to view it.

      

3. Terminal Command: Create a symbolic link in the /usr/local/lib directory:
   Note: replace “<_X_Y>” with the version numbers to match the directory name for the version you downloaded.

   If you are upgrading to a new version of OCI you will need to delete the old  symbolic link and recreate it to point to the new version of OCI.

   sudo ln -s /usr/local/instantclient<_X_Y>/libclntsh.dylib /usr/local/lib/

   1. The symbolic link will look like this:

      The /usr/local/lib directory is hidden in Finder, so use Go>Go to Folder and enter “/usr/local/lib” to view it

      

4. Delete the zip package (or keep a copy if you prefer).
5. Select the Oracle Instant Client (OCI) driver in Iguana:
   1. Open Iguana > Settings > Databases and click the Edit button for OCI Oracle:
      
   2. Select Automatically choose which OCI Oracle shared library to load, then click Save Changes:
      
   3. You will need to restart Iguana for the settings to take effect:
      
   4. Confirm that Iguana automatically finds the correct library:

      Iguana finds the symbolic link /usr/local/lib/libclntsh.dylib that we created in step (4). This is because /usr/local/lib is (one of the places) where the maOS automatically searches for dynamic library files.

      

Create an EZCONNECT String [top]

1. Get the following information:
   • username: Login user name for the Oracle server

     If the username and password are left blank then integrated security is used — this requires kerberos. You should speak to your DBA (database administrator) and/or your system/network administrator about if you have questions (about setting up permissions for) integrated users.

   • password: Password for the user
   • host: This name of the Oracle database server

     This must be a valid DNS name or IP address for the Oracle database server.

   • (Optional) port:

     If the port is not included then it defaults to 1521. In general if you are not given a port number then the default port (1521) should work.

   • service name:

     The service name (or database service name) is the “logical database name” that Oracle uses to access a database – it is equivalent to the database name in other database systems. If you are using Oracle 11g or later you can omit the service name and the default will be used — however we strongly recommend including the server name (for clarity).

   • (Optional) server:

     Never set this unless asked to by your DBA (database administrator).

   • (Optional) instance_name:

     Never set this unless asked to by your DBA (database administrator).

2. Create the EZCONNECT string:
   • Construct the string in this format: username/password@[//]host[:port][/service_name][:server][/instance_name]

     Items in [] square brackets are [optional] — the last two items [:server][/instance_name] are not required (unless your DBA advises you to use them).

   • For example:
     • username: FredSmith
     • password: secret-password
     • host: Oracle-Server
     • port: 3351
     • service name: Test-DB
     • EZCONNECT: FredSmith/secret-password@Oracle-Server:3351/Test-DB
   • Another example:

     This example is based on the Oracle DB Developer VM that can be downloaded from Oracle.

     • username: hr
     • password: oracle
     • host: oracle-linux
     • service name: orcl
     • EZCONNECT: hr/oracle@oracle-linux/orcl
3. Using an EZCONNECT string in Iguana Translator:
   1. Use the db.connect{} API function
   2. name = ‘[//]host[:port][/service_name][:server][/instance_name]’
   3. user = ‘username’
   4. password = ‘password’
4. For example EZCONNECT “hr/oracle@oracle-linux/orcl”:
   • Use the db.connect{} API function
   • name = ‘//oracle-linux/orcl’
   • user = ‘hr’
   • password = ‘oracle’
     

     Note: For this to work you will need to have installed the Oracle OCI drivers on your local machine.

Troubleshooting [top]

• Problems with connection settings:

  Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

• Iguana may not always support the very latest version of the OCI drivers:

  Please contact us at support@interfaceware.com if you need to know the latest supported version of the OCI drivers.

• If Iguana cannot find the library automatically:

  This is probably an issue with the symbolic link being in the incorrect directory. Speak to your system administrator and ensure that symbolic link is on the dynamic library search path. Alternatively you can use the Browse button to find the driver manually.

• Not enough permissions to load the Oracle Library:
  

  This error may mean that Iguana does not support the OCI library you are loading — try and older version. It can also be caused by dynamic libraries not finding their dependencies (se the next point). Otherwise speak to your system administrator to ensure you have full read/write/execute permissions for the OCI directories.

• If you cannot load the OCI drivers on Linux you may need to update the runtime link path for dynamic libraries:

  This tells the instantclient software where to look for dynamic library dependencies. Only do this if the instantclient is the only Oracle software you are installing. Do not do it if you are installing the Oracle database server on the same machine, instead discuss it with your DBA.

  Terminal Commands: Update the instant client configuration file:

  sudo sh -c "echo /opt/oracle/instantclient_18_3 > \ /etc/ld.so.conf.d/oracle-instantclient.conf"
  sudo ldconfig

If you need more help please contact us at support@interfaceware.com.

More Information [top]

• Using Database Connection Objects
• Connect to Oracle with OCI
• Connect to Oracle from Windows with ODBC
• Connect to Oracle from Linux with ODBC