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/tiger@'(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
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/CEC_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/OLS_web-min-scaled.jpg)