Oracle Windows Integration

A blue and white Windows logo.

Oracle is considered to be the most popular enterprise database. It has been available on Windows since 1994, and all of the most powerful and popular Oracle Database features are present on Windows. Oracle for Windows offers the benefit of operating Oracle in a familiar environment, and is fast to deploy for Windows IT experts.

Integration Overview

This Oracle Database integration is established in Windows, and for data visualization, it transfers a .ramdb file to Ubuntu to serve dashboards. This strategy simplifies security and administration for Oracle Server professionals.

A flow chart shows the process of transferring files.

The basic connection process involves using row64tools to push updates from an Oracle Database to Row64. An overview is available here:

An overview of securely transferring files 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 you’ll switch to the commercial Oracle Database Enterprise Edition once your test is working.

Install Oracle Linux For Windows

If any of the following Oracle environment variables are set, delete them:

  • ORACLE_HOME
  • TNS_ADMIN

Download the current version of Oracle Database Express Edition for Windows:

A screen with a red arrow pointing to a section of text that reads Windows .

Right-click OracleXE213_Win64.zip and select “Extract All.” Extract the files into a temporary location of your choosing.

In the extracted folder, double-click setup.exe.

Accept the default settings. When you are prompted to create a database password, use: temp7

A screen with a password prompt and a button to create a new password.

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

Continue to accept the default settings and complete the install.

A screen with a message that says Oracle Database Installed Successfully .

When the installation completes, it will provide the information needed to connect to the default pluggable database.

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 diagram shows a database with a blue box on top.

To get started, open the command prompt to begin creating a new default Pluggable Database (PDB). In the command prompt, type:

sqlplus sys/temp7@XE as sysdba

It should log you into an Oracle SQL command prompt:

A computer screen displaying a black screen with a message.

Create an Oracle User

Next, create the C##radmin7 user to access from Python. This user should use the temporary password: temp7

In the command prompt, type:

create user C##radmin7 identified by temp7;

Grant the privileges needed to log in and create tables. Note that we need to set container=all to have it work on pluggable databases:

grant create table to C##radmin7 container=all;
grant create session to C##radmin7 container=all;

Next, grant privileges to insert data into tables:

alter user C##radmin7 quota unlimited on users container=all;

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

grant create view, create procedure, create sequence to C##radmin7 container=all;

Exit the SQL Prompt so we can log in as a different user:

EXIT

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

Create Test Data

In the terminal, log in as the new C##radmin7 user:

sqlplus C##radmin7/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 robotic pool vacuums.

Exit the SQL Prompt:

EXIT

Install Python on Windows

To start, install the latest version of Python from:

A blue screen with a yellow background and a download link for Python 3.11.

Downloading the Python install manager is an easy approach because it installs the environment variables to run Python from the command line.

Once it’s installed, verify that the Python prompt is working in the command line. Open a command prompt and type:

python

You should see the Python prompt appear in the terminal. To exit the Python prompt, type:

quit

Next, set up the Python pip libraries needed for the integration. Type the following commands in the terminal, entering them one line at a time:

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

Set Up Login Credentials in .env

You will need to create a .env file from the command prompt and edit it in Notepad.

Use the following commands to create the .env file at the specified directory location. This directory can also be used to save .ramdb files.

mkdir C:\r64tools
cd C:\r64tools
.>db.env 2>NUL
notepad db.env

With the .env file open in Notepad, input the example setup data:

DBHost=localhost/xepdb1
DBUser=C##radmin7
DBPwd=temp7
SSH_Host=192.168.1.10
SSH_Port=22
SSH_User=row64
SSH_Pwd=temp7

You can update this configuration at a later point with the correct information for your particular setup. It is important to use SSH with the row64 user, because files need to be transferred with an account that has access to row64server.

Networking and SSH Setup

On the receiving server or instance, if you only completed the default Ubuntu installation, SSH will likely need to be manually installed.

First, check if OpenSSH is already installed. In Ubuntu, check the list of installed UFW profiles with:

sudo ufw app list

If OpenSSH is not listed, install it with:

sudo apt install openssh-server

Enable SSH connections and the firewall:

sudo ufw allow OpenSSH
sudo ufw enable

Loading Folder and Permissions

You will need to make the loading folder that recieves your .ramdb file. For this minimal example, use the following command to create the needed folder at the specified location:

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

Also, it’s best to set the SSH_User field to row64 so that when the copied file is received, the row64server service has access to it.

SSH_User=row64

Download Oracle Windows Integration

The Row64 integration for Oracle can be downloaded from:

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

C:\r64tools\Oracle_Windows_RamDB.py

In the terminal, run the Python integration with the following commands:

cd C:\r64tools
python Oracle_Windows_RamDB.py

The terminal should print the example table and display the status of the transfer.

A black screen with a message about a robotic pool vacuum.

Troubleshoot if Needed

In the event that you need to troubleshoot the connection, this section provides some tips that might help you identify the potential issue.

Environment File Configuration

When you ran Oracle_Windows_RamDB.py in an earlier section, it attempted to transfer the new .ramdb file using SCP to the server specified in:

C:\r64tools\db.env

For this to work, it is important to verify that the file is set up correctly.

Install Row64 Server

On the Ubuntu machine you are copying to, make sure you’ve installed Row64 Server. If you don’t already have Row64 Server installed, you can reference the following documentation to set it up:

Troubleshoot Loading Folder and Permissions

Ensure that you have created the loading folder and granted it the proper permissions.

Port Connections

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 data.

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

You can drag the .ramdb file right into the 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

On Windows, Task Scheduler is the production-proven tool for establishing continual updates. Here’s a simple example on how to set it up:

A diagram shows the relationship between a Windows Task Scheduler and a Row 644 tools.

You simply need to take the integration .py file and set up a cron job to run at your data refresh rate, which can range from every 20 seconds to every day.

ESC