About Domino
Domino Data LabKnowledge BaseData Science BlogTraining

Connect to Oracle Database

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.

Domino recommends that you use a Domino Data Source to connect to an Oracle instance from Domino.

Create an Oracle data source

  1. From the navigation pane, click Data.

  2. Click Create a Data Source.

  3. In the New Data Source window, from Select Data Store, select Oracle.

    oracle newds

  4. Enter the Host, Port, and name of the Database.

  5. Enter the Data Source Name.

  6. Optional: Enter a Description to explain the purpose of the data source to others.

  7. Click Next.

  8. Enter the Username and Password to connect to Oracle. The Domino secret store backed by HashiCorp Vault securely stores the credentials.

  9. Click Test Credentials.

  10. If the data source authenticates, click Next.

  11. Select who can view and use the data source in projects.

  12. Click Finish Setup.

Alternate way to connect to Oracle

Warning

  1. Before you can connect to Oracle, you must install the following client software your environment:

    • The basic package: instantclient-basic-linux.x64-<oracle-version>dbru.zip

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

      You must download download these files from the Instant Client Downloads page.

      1. Use your Oracle customer login.

      2. Host an internal mirror of the files somewhere accessible to your Domino hosts.

      3. Install the software manually. In the example environments in this topic, these files are retrieved from a private S3 bucket with Wget. You must make them available in a similar manner for your Domino deployment.

Python and cs_Oracle

  1. To interact with Oracle databases from Python, Domino recommends the cx_Oracle library.

  2. Use the following Dockerfile instruction to install the Oracle client drivers and cx_Oracle in your environment.

    Note
    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

  3. Set the following Domino environment variables to store secure information about your Oracle connection.

    • 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 previously.

      See Secure Credential Storage to learn more about Domino environment variables.

  4. See the cx_Oracle documentation for information about how to use the package. The following is an example to connect to Oracle with cx_Oracle where:

    • You have set up environment variables 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

  1. To interact with Oracle databases from R, Domino recommends the ROracle library.

  2. Use the following Dockerfile instruction to install the Oracle client drivers and RODBC in your environment.

    Note
    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

  3. Set up the following Domino environment variables to store secure information about your Oracle connection.

    • 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 previously.

      See Secure Credential Storage to learn more about Domino environment variables.

  4. See the ROracle documentation for usage details.

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