Tech Ecosystem
Domino Reference
About Domino
Domino Data LabKnowledge BaseData Science BlogCommunityTraining

Connect to Oracle Database from Domino

This topic 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.

Connect to Oracle with Domino Data Sources

The easiest way to connect to an Oracle instance from Domino is to use a Domino Data Source.

Configuration

To create an Oracle Data Source select Oracle as the Data Source type from the New Data Source wizard and specify the relevant parameters.

image

Authentication

Specify the credentials that will be used to connect to Oracle.

Currently, the only authentication mechanism supported is Username and Password. The credentials will be securely stored in the Domino secret store backed by HashiCorp Vault.

oracle creds

Make the Oracle drivers available to Domino

The following client software 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 topic, 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 following Dockerfile instruction 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.

USER root

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 -sf 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

USER ubuntu

Credential setup

There are several environment variables you must 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 about 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 following Dockerfile instruction 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..

USER root

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

USER ubuntu

Credential setup

There are several environment variables you must 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.

Domino Data LabKnowledge BaseData Science BlogCommunityTraining
Copyright © 2022 Domino Data Lab. All rights reserved.