Unix ODBC Oracle

To connect via ODBC, check https://laurentschneider.com/wordpress/tag/odbc

This article is related to Unix/Linux. Often you have a fat client written in C, while java uses JDBC instead of ODBC.

Okay, it’s pretty easy, if you have an oracle client, you probably already have libsqora.so.xx.1 in your LD_LIBRARY_PATH. In this case you can connect using ODBC.

What you need is an odbc.ini where you defined your connections
[DB01]
Driver = OracleODBC18
DSN = DB01
ServerName = DB01

and an odbcinst.ini where you define your driver
[OracleODBC18]
Description = Oracle ODBC driver for Oracle 18
Driver = /u01/app/oracle/product/18.1.0/client_64/lib/libsqora.so.18.1

the name / location and options may depend on your software / driver vendor.

ODBC uses TNSNAMES, so it really easy, you just the odbc entry ServerName = DB01 that matches
DB01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = srv01.example.com)(Port = 1521))(CONNECT_DATA = (SERVICE_NAME=DB01.example.com)))
in tnsnames.ora.

ODBC full client allows you to use all connection features like LDAP and SSL.

To test it, I installed unixODBC-devel
sudo yum install unixODBC-devel
export ODBCSYSINI=/home/user1/odbc
cd $ODBCSYSINI
vi odbc.ini odbcinst.ini

the machine-wide ODBCSYSINI is /etc. You can chose to define the ODBCINI user-wide (/home) and the ODBCSYSINI machine-wide (/etc). I wouldn’t use machine-wide passwords. But configuring the drivers only once may be an option. If you are root and you have not too many drivers/versions/bitcode.

Now try to connect :
isql DB01 scott tiger
SQL> select * from scott.emp;
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO|
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800 | | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00| 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00| 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00| 2975 | | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00| 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00| 2850 | | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00| 2450 | | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00| 3000 | | 20 |
| 7839 | KING | PRESIDENT| | 1981-11-17 00:00:00| 5000 | | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00| 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00| 1100 | | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00| 950 | | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00| 3000 | | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00| 1300 | | 10 |
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
SQLRowCount returns -1
14 rows fetched
SQL>