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.
The easiest way to connect to an Oracle instance from Domino is to use a Domino Data Source.
The following client software must be installed in your environment before you can connect to Oracle:
-
The basic package:
instantclient-basic-linux.x64-<oracle-version>dbru.zip
-
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.
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)
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.