Oracle Integration

A blue and white logo for row 644.

Oracle was one of the first relational database management systems (RDBMS). It is widely viewed as the most popular enterprise database, and is reputed to be a solid database manager since the 1970s. Oracle integrates easily with Row64 by wiring to Row64 RamDb through Python.

Integration Overview

This Oracle Database integration uses Oracle Linux and transfers the data to Ubuntu to serve dashboards. This approach simplifies security and enables Oracle Server administrators to work in a familiar environment.

Oracle Linux

A green background with a red logo for Oracle.

Since this example uses both Oracle Linux and Ubuntu, you might have the question: Why not have both the database and the dashboard run Oracle Linux? The answer is that you can; we’ve tested it, and it works well.

Here, you can see Row64 Server running on Oracle Linux

A computer screen displays a map of a city with green and blue labels.

So, if you’re interested in installing Row64 Server on Oracle Linux, simply follow the RedHat installation guide and return here when you’re finished.

Our standard and setup, shown in this guide, is to run the Oracle Database in Oracle Linux, and to use SCP to securely transfer the file to the dashboard on Ubuntu.

A flow chart shows the process of transferring files.

If you want to learn more about how Python can be used to call an Oracle database, you can read the following article:

The basic connection process is to use row64tools to push updates from an Oracle Database to Row64. For further reading, you can learn more about row64tools at:

An overview of secure file transfer using SCP in Python is described here:

Minimal Test Setup

This tutorial shows how to complete a quick test with the Oracle Database XE version. This version is for development tests and non-commercial work. It’s expected that you will switch to the commercial Oracle Database Enterprise Edition once you have your test working.

Install Oracle Linux

The current version of Oracle Database XE is for Oracle Linux 8.

Download Oracle Linux 8.10 from:

In this example, we will create a user, called row64, with administrative privileges. It’s not essential for you to work with this username, but it will simplify this example.

Install Oracle Database XE

After you have Oracle Linux installed and running, set up the Oracle Database XE pre-install in the terminal with:

sudo -s
dnf -y install oracle-database-preinstall-21c

Next, go to the Oracle XE software downloads page and download the Linux x64 ( OL8 ) version

Install it by using the following command:

dnf -y localinstall oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm

Set Up an Oracle Database

Set up an Oracle XE database with the default settings:

sudo -s
/etc/init.d/oracle-xe-21c configure

This will ask you for a single password for the admin accounts; SYS, SYSTEM, and PDBADMIN. Use the following as your password:

temp7

This is a simple password to match the Python in this example.

Verify Setup is Complete

Once your database setup is complete, it should give you a message that resembles the following example:

Connect to Oracle Database using one of the connect strings:
     Pluggable database: localhost.localdomain/XEPDB1
     Multitenant container database: localhost.localdomain
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

Set Oracle Environment Variables

In the terminal, enter the following lines:

sudo -s
export ORACLE_SID=XE 
export ORAENV_ASK=NO 
. /opt/oracle/product/21c/dbhomeXE/bin/oraenv

It will return with the following line, which is asking for the path to ORACLE_HOME:

ORACLE_HOME = [] ?

Paste the following path as a response:

/opt/oracle/product/21c/dbhomeXE

It should respond and confirm:

The Oracle base has been set to /opt/oracle

If you have any issues, a more detailed installation guide for Oracle XE is available here:

Run Oracle From The Terminal

In the terminal, type the following commands:

sudo -s
sqlplus SYSTEM/temp7@XE

This gives you an SQL Prompt for administration. This example is going to work with a different user, however, so exit the prompt with:

EXIT

Create A Pluggable Database User

It is not possible to connect to the database as SYSTEM to create the tables that are accessed from Python. This approach is insecure, so Oracle prevents it. Instead, you will first connect to the example pluggable database (XEPDB1) as sys as sysdba. From there, you will create a new user, radmin, which will be the account used to create and query tables from Python.

A blue box with a white background is labeled with the word plan .

Connect to the pre-made example pluggable database. In the terminal, type:

sqlplus sys/temp7@//localhost:1521/XEPDB1 as sysdba

(Alternatively, the following command is more simple. You can use either the preceding or present command to connect to the XEPDB1, but there’s no need to run both):

sqlplus sys/temp7@XE as sysdba

After connecting to the XEPDB1, create the radmin user to access from Python. We will give this user a temporary password: temp7

create user radmin identified by temp7;

Next, grant privileges needed to log in and create tables:

grant create table to radmin;
grant create session to radmin;

Grant privileges to insert data into tables:

alter user radmin quota unlimited on users;

Finally, give the full privileges needed for: creating views, creating procedures, and creating sequences. This is only for our example; in production, you should give users the least amount of privileges they need to operate.

grant create view, create procedure, create sequence to radmin;

If you have any trouble, more details on this process are available here:

Create Test Data

We need to create test data for Python to query and transfer to Row64.

In the terminal, log in as the new radmin user:

sqlplus radmin/temp7@//localhost:1521/XEPDB1

Create a new table with test data. Enter each line one at a time:

CREATE TABLE sales (tdate TIMESTAMP, amount DECIMAL(15,2), product VARCHAR2(50));
INSERT INTO sales (tdate,amount,product) VALUES (TIMESTAMP'2025-01-21 13:23:44', 446.57, 'Seal SE Robotic Pool Vacuum');
INSERT INTO sales (tdate,amount,product) VALUES (TIMESTAMP'2025-01-21 13:23:52', 287.24, 'Tikom Robot Vacuum and Mop');
INSERT INTO sales (tdate,amount,product) VALUES (TIMESTAMP'2025-01-21 13:24:03', 1376.28, 'ECOVACS DEEBOT T20 Omni Robot Vacuum');
INSERT INTO sales (tdate,amount,product) VALUES (TIMESTAMP'2025-01-21 13:24:17', 3200.10, 'Husqvarna Automower 430XH');

Query some sample data from the table:

SELECT tdate,amount,product FROM sales;
A computer screen displaying a list of products.

Exit the SQL Prompt:

EXIT

Set Up Python 3.14

Oracle Python Libraries require Python 3.8+, but the current XE version uses Python 3.6. Because of this, we need to install a second Python.

It’s also best practice to install a second Python to avoid pip installs corrupting the OS python calls.

For our next step, we will use the standard approach for managing two instances of Python, which is accomplished through pyenv. To get started, install the dependencies for pyenv. Run these in the terminal, one line at a time:

sudo yum install libffi-devel -y
sudo yum install zlib-devel -y
sudo yum install bzip2-devel -y
sudo yum install readline-devel -y
sudo yum install sqlite-devel -y
sudo yum install wget -y
sudo yum install llvm -y
sudo yum install ncurses-devel -y
sudo yum install openssl-devel -y
sudo yum install redhat-rpm-config -y
sudo yum install xz xz-devel -y

We’ll also need libyaml-devel. Get this by enabling the “PowerTools” repository equivalent:

sudo dnf config-manager --set-enabled ol8_codeready_builder
sudo yum install libyaml-devel -y

Our next dependency is the dev tools needed to compile latest Python on install:

sudo yum groupinstall "Development Tools" -y

Finally, set up git and run the pyenv installer:

sudo yum install git -y
curl -L https://github.com/pyenv/pyenv-installer/raw/master/bin/pyenv-installer | bash

Append pyenv initializator to your “.bashrc” file:

export PATH="~/.pyenv/bin:$PATH"
eval "$(pyenv init -)"
eval "$(pyenv virtualenv-init -)"

Source the “.bashrc” file to continue the setup in the same terminal:

source ~/.bashrc

Install Python 3.14 and set it as the global version:

pyenv install 3.14
pyenv global 3.14

It might issue a warning about _tkinter not installing, but that is only needed for matplotlib plots, which we will not be using.

Install Python Pip Libraries

Install the Python libraries used to connect to the database and transfer a .ramdb file. In the terminal, type:

pip install row64tools
pip install python-dotenv
pip install oracledb
pip install paramiko
pip install scp

Set Up .env For Security

Set up a .env file to seperate the login credentials from the .py files. More details on this approach are found here:

Make a directory to store the .env in the user account. This example is using the row64 user.

mkdir /home/row64/r64tools

We will also use this directory to save .ramdb files. Of course, this is just an example, so you can modify these paths however you want.

Create a db.env file and use gedit to modify the content (the following command both creates and opens the file):

gedit /home/row64/r64tools/db.env

Type in the example setup credentials:

DBHost=localhost/xepdb1
DBUser=radmin
DBPwd=temp7
SSH_Host=192.168.1.10
SSH_Port=22
SSH_User=row64
SSH_Pwd=temp7

Replace the SSH values with the credentials of your installed instance of Row64 Server that you want to copy files to.

  • SSH_Host
  • SSH_Port
  • SSH_User
  • SSH_Pwd

Download Row64 Oracle Integration

The Row64 integration for Oracle can be downloaded from:

Download the integration and copy the Oracle_RamDB.py file into the folder location:

/home/row64/r64tools/Oracle_RamDB.py

Run the Python integration with:

cd /home/row64/r64tools
python Oracle_RamDB.py

You should see the example table print to the terminal, and it should output the status of the transfer.

A computer screen shows a file with the name robotic pool .

Ensure that, when you transfer the file from Oracle Linux to Ubuntu, you make the directory to recieve the file in advance. For example, if you copy into the folder:

/var/www/ramdb/live/RAMDB.Row64/Temp/Test.ramdb

Make sure:

  • You created the folder in Linux
  • You copy the file so that the dashboard (user: row64) has access to it

Set Up SSH on Ubuntu

If you want to SCP copy into your Ubuntu server with Row64, you need to make sure SSH is installed on it.

First, check the list of installed UFW profiles with:

sudo ufw app list

If OpenSSH is not listed, then install it. Ubuntu Desktop versions don’t install with OpenSSH.

sudo apt install openssh-server

Then, enable SSH connections and the firewall:

sudo ufw allow OpenSSH
sudo ufw enable

Finally, make sure you have created the loading folder you want to copy into. More details on how the live folder works are here:

Here’s an example of making a live folder on Ubuntu. Run this as the row64 user:

mkdir -p /var/www/ramdb/loading/RAMDB.Row64/Temp

Debug SSH to Ubuntu if Needed

If the .ramdb file didn’t copy over to Row64 Server, debug the port connections with ping and telnet. In the following commands, substitute the example IP addresses (192.168.1.10) with your own hostnames or IPs:

Ping:

ping 192.168.1.10

Telnet:

telnet 192.168.1.10 22

For reference, the following article discusses resolving these issues:

Test With ByteStream Viewer

Once you see the file copy over to Ubuntu, you can install ByteStream Viewer to visualize the file.

To install ByteStream Viewer on Ubuntu, you can reference the following documentation:

You can drag the .ramdb file directly into ByteStream Viewer.

A computer screen with a window open to a product page.

Alternatively, you can simply open the file in Row64 Studio.

Continual Updates

Cron jobs are the simple and production-proven Linux tool for continual updates.

Here’s a simple example on how to set them up:

All you need to do is take the integration .py file and set up a cron job to run it at your data refresh rate, from every day to every 20 seconds.

A blue and white logo for cron.

If your update rate is faster than 60 seconds, be sure to update your row64 config in:

/opt/row64server/conf/config.json

Update “RAMDB_UPDATE” to match the update speed.

ESC