Overview

This article describes how to connect to Oracle from Domino.

Oracle Database is a proprietary relational database available as a cloud service or enterprise on-premises solution.

Making the Oracle drivers available to Domino

There are two pieces of client software that must be installed in your environment before you can connect to Oracle:

1. The basic package: instantclient-basic-linux.x64-<oracle-version>dbru.zip
2. The SDK package: instantclient-sdk-linux.x64-<oracle-version>dbru.zip

This software is not hosted by Oracle in a way that permits programmatic installation. You will need to download these files from the Instant Client Downloads page using your Oracle customer login, then host an internal mirror of the files somewhere accessible to your Domino hosts.

In the example environments shown in this article, you will see that these files are retrieved from a private S3 bucket with wget. You will need to make them available in a similar manner for your Domino deployment.

Python and cx_Oracle

Domino recommends the cx_Oracle library for interacting with Oracle databases from Python.

Environment setup

Use the Dockerfile instruction below to install the Oracle client drivers and cx_Oracle in your environment. Note that you cannot copy and paste this Dockerfile directly, as you need to set up your own internal host of the Oracle clients and modify the wget step shown here to retrieve them.

RUN \
    wget https://s3-us-west-2.amazonaws.com/<s3-bucket-name>/instantclient-basic-linux.x64-12.1.0.2.0.zip \
         -O /home/ubuntu/instantclient-basic-linux.x64-12.1.0.2.0.zip && \
    wget https://s3-us-west-2.amazonaws.com/<s3-bucket-name>/instantclient-sdk-linux.x64-12.1.0.2.0.zip \
         -O /home/ubuntu/instantclient-sdk-linux.x64-12.1.0.2.0.zip && \
    cd /home/ubuntu && \
    unzip instantclient-basic-linux.x64-12.1.0.2.0.zip && \
    unzip instantclient-sdk-linux.x64-12.1.0.2.0.zip && \
    mv instantclient_12_1 /usr/local/lib && \
    rm instantclient-basic-linux.x64-12.1.0.2.0.zip && \
    rm instantclient-sdk-linux.x64-12.1.0.2.0.zip && \
    apt-get install -y libaio1

RUN \
    echo 'export OCI_LIB=/usr/local/lib/instantclient_12_1' \
          >> /home/ubuntu/.domino-defaults && \
    echo 'export OCI_INC=/usr/local/lib/instantclient_12_1/sdk/include' \
          >> /home/ubuntu/.domino-defaults && \
    echo 'export LD_LIBRARY_PATH=/usr/local/lib/instantclient_12_1:$LD_LIBRARY_PATH' \
          >> /home/ubuntu/.domino-defaults

RUN \
    cd /usr/local/lib/instantclient_12_1 && \
    ln -s libclntsh.so.12.1 libclntsh.so && \
    chown -R ubuntu:ubuntu /usr/local/lib/instantclient_12_1

RUN \
    echo '/usr/local/lib/instantclient_12_1' \
          > /etc/ld.so.conf.d/oracle-instantclient.conf && \
    ldconfig -v

RUN pip install cx_Oracle --upgrade

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your Oracle connection. Set the following as Domino environment variables on your user account:

• ORACLE_HOST

  Hostname where your database is running. Make sure your Oracle host and network firewall are configured to accept connections from Domino.

• ORACLE_SERVICE

  The service name of the Oracle service running on the target host.

• ORACLE_USER

  The Oracle user you want to authenticate as.

• ORACLE_PASSWORD

  Password for the user specified above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the cx_Oracle documentation for detailed information on how to use the package. Below is a simple example for connecting to Oracle with cx_Oracle where:

• you have set up environment variables noted above with the hostname, service name, username, and password
• your user has access to a database named houses in the target Oracle instance

from __future__ import print_function
import cx_Oracle
import os

# fetch values from environment variables and set the target database
hostname = os.environ['ORACLE_HOST']
service  = os.environ['ORACLE_SERVICE']
username = os.environ['ORACLE_USER']
password = os.environ['ORACLE_PASSWORD']
connection_string = hostname + "/" + service

# Connect as user "hr" with password "welcome" to the "oraclepdb" service running on this computer.
connection = cx_Oracle.connect(username, password, connection_string)

cursor = connection.cursor()
cursor.execute("""
    SELECT address
    FROM houses
    WHERE zip = 90210""")
for address in cursor:
    print("Address:", address)

R and ROracle

Domino recommends the ROracle library for interacting with Oracle databases from R.

Environment setup

Use the Dockerfile instruction below to install the Oracle client drivers and RODBC in your environment. Note that you cannot copy and paste this Dockerfile directly, as you need to set up your own internal host of the Oracle clients and modify the wget step shown here to retrieve them..

RUN \
    wget https://s3-us-west-2.amazonaws.com/<s3-bucket-name>/instantclient-basic-linux.x64-12.1.0.2.0.zip \
         -O /home/ubuntu/instantclient-basic-linux.x64-12.1.0.2.0.zip && \
    wget https://s3-us-west-2.amazonaws.com/<s3-bucket-name>/instantclient-sdk-linux.x64-12.1.0.2.0.zip \
         -O /home/ubuntu/instantclient-sdk-linux.x64-12.1.0.2.0.zip && \
    cd /home/ubuntu && \
    unzip instantclient-basic-linux.x64-12.1.0.2.0.zip && \
    unzip instantclient-sdk-linux.x64-12.1.0.2.0.zip && \
    mv instantclient_12_1 /usr/local/lib && \
    rm instantclient-basic-linux.x64-12.1.0.2.0.zip && \
    rm instantclient-sdk-linux.x64-12.1.0.2.0.zip && \
    apt-get install -y libaio1

RUN \
    echo 'export OCI_LIB=/usr/local/lib/instantclient_12_1' \
          >> /home/ubuntu/.domino-defaults && \
    echo 'export OCI_INC=/usr/local/lib/instantclient_12_1/sdk/include' \
          >> /home/ubuntu/.domino-defaults && \
    echo 'export LD_LIBRARY_PATH=/usr/local/lib/instantclient_12_1:$LD_LIBRARY_PATH' \
          >> /home/ubuntu/.domino-defaults

RUN \
    cd /usr/local/lib/instantclient_12_1 && \
    ln -s libclntsh.so.12.1 libclntsh.so && \
    chown -R ubuntu:ubuntu /usr/local/lib/instantclient_12_1

RUN \
    echo '/usr/local/lib/instantclient_12_1' \
          > /etc/ld.so.conf.d/oracle-instantclient.conf && \
    ldconfig -v

RUN \
    cd /home/ubuntu && \
    wget https://cran.r-project.org/src/contrib/ROracle_1.3-1.tar.gz && \
    R CMD INSTALL --configure-args='--with-oci-inc=/usr/local/lib/instantclient_12_1/sdk/include --with-oci-lib=/usr/local/lib/instantclient_12_1' ROracle_1.3-1.tar.gz

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your Oracle connection. Set the following as Domino environment variables on your user account:

• ORACLE_HOST

  Hostname where your database is running. Make sure your Oracle host and network firewall are configured to accept connections from Domino.

• ORACLE_SERVICE

  The service name of the Oracle service running on the target host.

• ORACLE_USER

  The Oracle user you want to authenticate as.

• ORACLE_PASSWORD

  Password for the user specified above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the ROracle documentation for usage details.