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.
-
From the navigation pane, click Data.
-
Click Create a Data Source.
-
In the New Data Source window, from Select Data Store, select Oracle.
-
Enter the Host, Port, and name of the Database.
-
Enter the Data Source Name.
-
Optional: Enter a Description to explain the purpose of the data source to others.
-
Click Next.
-
Enter the Username and Password to connect to Oracle. The Domino secret store backed by HashiCorp Vault securely stores the credentials.
-
Click Test Credentials.
-
If the data source authenticates, click Next.
-
Select who can view and use the data source in projects.
-
Click Finish Setup.
-
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.
-
Use your Oracle customer login.
-
Host an internal mirror of the files somewhere accessible to your Domino hosts.
-
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
-
To interact with Oracle databases from Python, Domino recommends the cx_Oracle library.
-
Use the following Dockerfile instruction to install the Oracle client drivers and cx_Oracle in your environment.
NoteUSER 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
-
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.
-
-
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
-
To interact with Oracle databases from R, Domino recommends the ROracle library.
-
Use the following Dockerfile instruction to install the Oracle client drivers and RODBC in your environment.
NoteUSER 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
-
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.
-
-
See the ROracle documentation for usage details.