Use Spider ODBC to connect to Oracle

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):

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

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

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

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

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

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

• 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