Use Spider ODBC to connect to Oracle
Overview
This article will help you to use Spider ODBC to connect to Oracle. Which can ease the pain of a migration by not requiring a wholesale cut over and enable a piecemeal approach.
Note: This was tested on CentOS 7 AWS EC2 t3a.Medium and a t3.small RDS Oracle 19 database. This is assuming you are just doing a simple install of MariaDB. Also, the following schema was used to populate the Oracle database: Oracle Sample Database.
Instructions
In your working directory create a new folder to hold the Oracle ODBC
RPMs mkdir oracle_odbc_rpms
and then change into this directoryDownload the following files (be sure to use the appropriate driver for your scenario):
Install the RPMs:
Put the following into /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
Put the following
into /etc/odbc.ini
:
Driver
should be the driver name in the/etc/odbcinst.ini
to have the correct driver path usedDescription
can be whatever you want to call it, just make sure you use the correct value laterServerName
is the TNSName that we give for our Oracle connectionBe sure to populate your user name and password
Put the following in
/etc/tnsnames.ora
:
You can call this connection string whatever you want, just be sure to use the same name in the odbc.ini file
Be sure to put in your host name/ip address plus the service name for your instance
Test that the ODBC connection is working with:
isql -v ORARDS
ORARDS is the section name we gave our entry in odbc.ini. If you used something different replace ORARDS with whatever you used
If there are any errors, double check the values entered into the .ini files
Start the MariaDB database with, e.g.:
systemctl start mariadb or systemctl start mysqld
Log into MariaDB and run the following:
This will create a new database, and a table that should be able to connect to an Oracle server via ODBC
dsn
must be the same as what we put in theodbc.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, and it prefers upper case
You should now be able to select data from
spider_test.contacts
table, which is coming from the remote Oracle database
Note: Currently Spider ODBC to Oracle tables does not support INSERT SELECT statements, where the Spider table is the source of the data. (MENT-1588).
Warning: If the version of MariaDB does not have the spider_direct_aggregate variable, then you will not be able to use a few aggregate functions on their own. (MENT-1558).
Note: 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).
This page is: Copyright © 2025 MariaDB. All rights reserved.
Last updated
Was this helpful?