This guide details how to configure Spider to connect to an Oracle database via ODBC, enabling data migration and federated access to Oracle tables.
This article describes how to use Spider ODBC to connect to Oracle. This can make it easier to migrate, by not requiring a wholesale cutover, but instead enabling a piecemeal approach.
The setup looks as shown in this figure:
In MariaDB,
The Spider storage engine must be installed.
The Spider storage engine plugin must be installed in MariaDB Enterprise Server. This is described in the instructions below.
An ODBC driver manager, unixODBC, must be installed.
In Oracle Database,
The ODBC driver must be installed.
The Oracle Basic Client must be installed. This is a prerequisite for installing the ODBC driver.
In your working directory, create a folder to hold the Oracle ODBC RPMs mkdir oracle_odbc_rpms, then change to that directory.
Download the following files (make sure to use the appropriate driver for your scenario):
Install the RPMs:
Spider ODBC to Oracle tables does not support INSERT SELECT statements, where the Spider table is the source of the data. (MENT-1588).
If the version of MariaDB does not have the variable, you aren't able to use a few aggregate functions on their own. (MENT-1558).
If you get the following error message: Error from ODBC 0 01004 [Oracle][ODBC]String data, right truncated., you need to set a larger value for the variable . (MENT-1557).
Blog post by Anders Karlsson, January 2024 - 13 minute read
This page is: Copyright © 2025 MariaDB. All rights reserved.
Add the following to /etc/odbcinst.ini:
The driver path may be different if you downloaded a different version of the Oracle driver, be sure to update it with correct path for your scenario.
Add the following to /etc/odbc.ini:
Driver is the driver name in the /etc/odbcinst.ini .
Description can be whatever you like, just make sure you use the same value later.
ServerName is the TNSName given for our Oracle connection.
Be sure to populate your user name and password.
Add the following line to /etc/tnsnames.ora:
Name the connection string as you like — just be sure to use the same name in the odbc.ini file.
Make sure to include your hostname and IP address, and the service name for your instance.
Verify that the ODBC connection is working with:
ORARDS is the section name we gave our entry in odbc.ini. If you are using something different, replace ORARDS with whatever you use.
If any errors come up, double-check the values entered into the .ini files.
Start the MariaDB database:
Log in to MariaDB, and run the following:
This creates a new database and a table that should be able to connect to an Oracle server via ODBC.
DSN must be the same put in the odbc.ini file .
It's best to always use upper case for both column names and the table value in the comment section, because these values are passed directly to Oracle Database, which prefers upper-case object names.
You should now be able to select data from spider_test.contacts table. That data is coming from the remote Oracle database.
[oracle]
Description = Oracle ODBC Connection
Driver = /usr/lib/oracle/19.16/client64/lib/libsqora.so.19.1[ORARDS]
Description = Oracle
Driver = oracle
ServerName = REMOTE
UserID = {user_name}
Password = {password}
TNSNamesFile = /etc/tnsnames.oraREMOTE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host_name/ip address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service_name})))isql -v ORARDS systemctl start mariadb or systemctl start mysqld INSTALL SONAME 'ha_spider';
CREATE DATABASE spider_test;
USE spider_test;
CREATE OR REPLACE TABLE spider_test.contacts
(
CONTACT_ID BIGINT NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR( 255 ) NOT NULL,
LAST_NAME VARCHAR( 255 ) NOT NULL,
EMAIL VARCHAR( 255 ) NOT NULL,
PHONE VARCHAR( 20 ) ,
CUSTOMER_ID BIGINT) ENGINE=SPIDER
CONNECTION='WRAPPER "odbc", DSN "ORARDS", table "CONTACTS"';wget https://download.oracle.com/otn_software/linux/instantclient/1916000/oracle-instantclient19.16-odbc-19.16.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1916000/oracle-instantclient19.16-sqlplus-19.16.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1916000/oracle-instantclient19.16-basic-19.16.0.0.0-1.x86_64.rpmyum localinstall *.rpm