This article is part of a series that includes SQLite, Postgresql, Firebird, MongoDB, Microsoft SQL Server, HSQLDB, Excel, and MariaDB. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
As we already have a running Oracle remote instance, we don’t need to set one up and only the ODBC drivers need to be installed.
As user debian, get and install the ODBC drivers for Oracle (an account is needed), cf here.

$ id
uid=1000(debian) gid=1000(debian) groups=1000(debian)
$ cd ~/Downloads
$ wget https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-basiclite-linux.x64-21.3.0.0.0.zip
$ wget https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-sqlplus-linux.x64-21.3.0.0.0.zip
$ wget https://download.oracle.com/otn_software/linux/instantclient/213000/instantclient-odbc-linux.x64-21.3.0.0.0.zip
$ mkdir ~/odbc4gawk
$ export workdir=~/odbc4gawk
$ mkdir $workdir
$ unzip instantclient-basiclite-linux.x64-21.3.0.0.0.zip -d ${workdir}/.
$ unzip instantclient-sqlplus-linux.x64-21.3.0.0.0.zip -d ${workdir}/.
$ unzip instantclient-odbc-linux.x64-21.3.0.0.0.zip -d ${workdir}/.

The instant client software is required; we also download and install Oracle’s native command-line administration tool sqlplus to populate the test schema.
Follow the installation instructions here.
Add the Instant Client path to the shared library path:

$ vi ~/.bashrc
export LD_LIBRARY_PATH=/home/debian/odbc4gawk/instantclient_21_3:$LD_LIBRARY_PATH

Note: If WordPress does not render them correctly, there is an underscore between the name, 21 and 3 in instantclient_21_3 above.
As there is a bug in the Oracle script odbc_update_ini.sh, a work-around is provided here.

cd $workdir/instantclient_21_3
$ mkdir etc
$ cp /etc/odbcinst.ini etc/.
$ cp ~/.odbc.ini etc/odbc.ini

Run the configuration script now:

$ ./odbc_update_ini.sh .

Oracle has updated the local copy of the odbcinst.ini file. Let’s copy it to /etc to make the changes system-wide:

$ sudo cp etc/odbcinst.ini /etc/.

Check the ODBC driver file /etc/odbcinst.ini:

$ odbcinst -q -d
...
[Oracle 21 ODBC driver]

Correct.
See here for further configuration of the ODBC driver.
Let’s test the connection to the remote db via the instant client:

$ cd ${workdir}/instantclient_21_3
$ ./sqlplus scott/[email protected]'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))'

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Oct 18 19:34:07 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Sat Oct 16 2021 01:17:00 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

The remote database is available and reachable natively.
Still in the sqlplus session, let’s populate the schema scott with the sample data. As the sample’s date values assume a different format, let’s switch to it in the Oracle session and avoid formatting errors:

SQL> alter session set nls_date_format = 'yyyy-mm-dd';

Let’s download the tables creation script using from here and the data populating script from here:

wget https://www.sqltutorial.org/wp-content/uploads/2020/04/oracle.txt --output-document=oracle.sql
wget https://www.sqltutorial.org/wp-content/uploads/2020/04/oracle-data.txt --output-document=oracle-data.sql

Create the tables and load the data now:

@oracle
@oracle-data

-- test the query:
set pagesize 10000
set linesize 200
set tab off
col country_name format a25
col STREET_ADDRESS format a30
col city format a20
SQL> SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')';

COUNTRY_NAME              CO CO STREET_ADDRESS                 CITY
------------------------- -- -- ------------------------------ --------------------
United States of America  US US 2014 Jabberwocky Rd            Southlake
United States of America  US US 2011 Interiors Blvd            South San Francisco
United States of America  US US 2004 Charade Rd                Seattle
United Kingdom            UK UK 8204 Arthur St                 London
United Kingdom            UK UK Magdalen Centre, The Oxford Sc Oxford
                                ience Park

China                     CN

6 rows selected.
SQL> quit

The test data are ready.
Let’s edit debian’s ODBC DSN definitions and add the settings below:

$ vi ~/.odbc.ini
...
[OracleODBC-21]
...
Driver=Oracle 21 ODBC driver
DSN=OracleODBC-21
...
ServerName=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
...
UserID=scott
Password=tiger

Check the DSN:

$ odbcinst -q -s -n "OracleODBC-21" 
[OracleODBC-21]
AggregateSQLType=FLOAT
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
...
ServerName=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
...
UserID=scott
Password=tiger

Test it using the ODBC Driver Manager test tool, isql:

$ isql -v OracleODBC-21
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN')
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| COUNTRY_NAME                            | COUNTRY_ID| COUNTRY_ID| STREET_ADDRESS                          | CITY          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| China                                   | CN        |           |                                         |               |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns -1
6 rows fetched

The ODBC connection is OK. Test the DSN with the python ODBC module pyodbc:

$ python3
import pyodbc 
cnxn = pyodbc.connect('DSN=OracleODBC-21')
cursor = cnxn.cursor()	
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('China', 'CN', None, None, None)
>>> 

Oracle is now fully accessible via ODBC under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
MongoDB
Microsoft SQLServer for Linux
Excel