Use Spider ODBC to Connect to Oracle

This guide details how to configure Spider to connect to an Oracle database via ODBC, enabling data migration and federated access to Oracle tables.

Overview

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:

Prerequisites

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.

The following procedure has been tested on CentOS 7 AWS EC2 t3a.Medium and a t3.small RDS Oracle 19 database. It assumes you are just doing a simple install of MariaDB. Also, the following schema is used to populate the Oracle database: Oracle Sample Database.

Instructions

1

In your working directory, create a folder to hold the Oracle ODBC RPMs mkdir oracle_odbc_rpms, then change to that directory.

2

Download the following files (make sure to use the appropriate driver for your scenario):

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

3

Install the RPMs:

yum localinstall *.rpm

4

Add the following to /etc/odbcinst.ini:

[oracle]
Description = Oracle ODBC Connection
Driver = /usr/lib/oracle/19.16/client64/lib/libsqora.so.19.1

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.

5

Add the following to /etc/odbc.ini:

[ORARDS]
Description = Oracle
Driver = oracle
ServerName = REMOTE
UserID = {user_name}
Password = {password}
TNSNamesFile = /etc/tnsnames.ora

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

6

Add the following line to /etc/tnsnames.ora:

REMOTE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={host_name/ip address})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME={service_name})))

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

7

Verify that the ODBC connection is working with:

isql -v ORARDS 

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

8

Start the MariaDB database:

systemctl start mariadb or systemctl start mysqld 

9

Log in to MariaDB, and run the following:

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"';

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

10

You should now be able to select data from spider_test.contacts table. That data is coming from the remote Oracle database.

Notes

• 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 spider_direct_aggregate 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 spider_buffer_size. (MENT-1557).

See Also

• Connecting to Oracle from MariaDB Enterprise Server using Spider Blog post by Anders Karlsson, January 2024 - 13 minute read

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}