Connect to Oracle with Easy Connect

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

Note: Using Easy Connect (EZCONNECT) will work in most cases — but you should check with the DBA (database administrator). According to Oracle Easy Connect is not recommended for complex systems.

Quote from the Oracle documentation: “Easy Connect naming is not suitable for large or complex environments with advanced features, such as connection pooling, external procedure calls, or Heterogeneous Services, that require additional connect information. In these cases, another naming method is recommended.”

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:

operating system OCI library Name
Linux libclntsh.so
Mac OS X libclntsh.dylib
Windows oci.dll

Note: Iguana may not always support the very latest version of Oracle drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of Oracle drivers are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

Tip: If you install the Oracle OCI drivers (and other database drivers) in the correct/default global driver directories for your operating system then Iguana can find them “automatically”. This is the preferred method — particularly for production servers.

However you can also install drivers in any location and manually configure Iguana to use them. This may be useful for development and testing.

Windows:

Note: The procedure and screenshots in this section are from Windows 10 — and may differ somewhat if you are using a different version of Windows.

Tip: If you wish to restrict the Oracle OCI driver access to a specific user you can add the location of the the drivers to the User Path variable instead of the System Path variable.

  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:
      tnsnames.ora files
  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:
        oracle directory
      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:
          extract OCI drivers
      3. The drivers are extracted into a new directory in c:\Oracle that looks something like this:
        OCI install directory
      4. Inside this directory there is an instantclient_X_Y drivers directory, like this:
        instantclient directory
      5. Move this directory to c:\Oracle.
      6. Delete the empty folder the zip was originally unpacked into:
        delete empty zip folder
      7. There should now be a single folder in c:\Oracle containing the OCI drivers:
        instantclient folder
  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:
      edit the envirnment variables
    2. The System Properties window will open on the Advanced page:
      system properties advanced
    3. Click the Environment Variables button near the bottom of the page:
      environment variables button
    4. Highlight the System Path variable and click Edit:
      highlight system path
    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:
      oci system variable path
  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:
      edit the envirnment variables
    2. The System Properties window will open on the Advanced page:
      system properties advanced
    3. Click the Environment Variables button near the bottom of the page:
      environment variables button
    4. If TNS_ADMIN does not exist then click New… to create it, or click Edit if it already exists:
      add or edit TNS_NAMES
    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.

      TNS_ADMIN update path

  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:
      edit oci
    2. Select Automatically choose which OCI Oracle shared library to load, then click Save Changes:
      select OCI library to load
    3. You will need to restart Iguana for the settings to take effect:
      restart Iguana for OCI settings
    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).

      OCI driver

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.

Note: The screenshots in this section are from CentOS 7 — and may differ somewhat if you are using a different version of Linux.

Tip: If you wish to restrict the Oracle OCI driver access to a specific user you can install them in the <home>/lib directory instead. You will also need to create the symbolic link in the <home>/lib directory. If the <home>/lib if does not exist you will need to create it.

  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:
        confirm install

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

        ls instantclient*

        You should see the instant client driver files like this:
        instan client driver files

    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:
        link to OCI

    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:
      edit oci
    2. Select Automatically choose which OCI Oracle shared library to load, then click Save Changes:
      select OCI library to load
    3. You will need to restart Iguana for the settings to take effect:
      restart Iguana for OCI settings
    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.

      oci lib file

Mac:

Note: The procedure and screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

Tip: If you wish to restrict the Oracle OCI driver access to a specific user you can install them in the <home>/lib directory instead. You will also need to create the symbolic link in the <home>/lib directory. If the <home>/lib if does not exist you will need to create it.

  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.

      instant client directory

  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

      instant client symbolic link

  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:
      select oracle driver
    2. Select Automatically choose which OCI Oracle shared library to load, then click Save Changes:
      select OCI library to load
    3. You will need to restart Iguana for the settings to take effect:
      restart Iguana for OCI settings
    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.

      iguana finds OCI symbolic link

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’
      oracle easy connect

      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:
    not enough permissions

    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]

Leave A Comment?