Use Spider ODBC to connect to Oracle - Source

Revision: 151081
User: Shwetha CM
Date: 2025-05-05 22:57

<<toc>>

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

<<style class="bluebox">>
**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: [[https://www.oracletutorial.com/getting-started/oracle-sample-database/|Oracle Sample Database]].
<</style>>

= Instructions

1. In your working directory create a new folder to hold the Oracle ODBC ##RPMs mkdir oracle_odbc_rpms## and then change into this directory

2. Download the following files (be sure to use the appropriate driver for your scenario):

<<code>>
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
<</code>>

3. Install the RPMs:

<<code>>
yum localinstall *.rpm
<</code>>

4. Put the following into /etc/odbcinst.ini:

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

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. Put the following ##into /etc/odbc.ini##:

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

* ##Driver## should be the driver name in the ##/etc/odbcinst.ini## to have the correct driver path used
* ##Description## can be whatever you want to call it, just make sure you use the correct value later
* ##ServerName## is the TNSName that we give for our Oracle connection
* Be sure to populate your user name and password

6. Put the following in ##/etc/tnsnames.ora##:

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

* 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

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

8. Start the MariaDB database with, e.g.: ##systemctl start mariadb or systemctl start mysqld##

9. Log into MariaDB and run the following:

<<code>>
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 COMMENT='wrapper "odbc", dsn "ORARDS", table "CONTACTS"';
<</code>>

* 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 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, and it prefers upper case

10. You should now be able to select data from ##spider_test.contacts## table, which is coming from the remote Oracle database

<<style class="bluebox">>
**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]]).
<</style>>

<<style class="bluebox">>
**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]]).
<</style>>

<<style class="bluebox">>
**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]]).
<</style>>

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Use Spider ODBC to connect to Oracle - Source

Products

Solutions

Resources

Company

Pricing

Download MariaDB