-
Notifications
You must be signed in to change notification settings - Fork 2.2k
How to connect to Oracle DB and SQL Server using ODBC
In order to work with Oracle DB or SQL Server using ODBC, follow these steps:
If you have not installed Docker on your machine, you can install it by going through the official documentation from Docker.
Verify installation by running:
$ docker -v
Docker version 24.0.6, build ed223bc
To be able to pull Oracle Database Docker container, create account on Oracle and accept terms and conditions.
Pull Oracle DB from Oracle Container registry
docker login container-registry.oracle.com
docker pull container-registry.oracle.com/database/express:latest
For arm based machines version 19.19.0.0 needs to be pulled:
docker pull container-registry.oracle.com/database/enterprise:19.19.0.0
Verify if container is pulled successfully
$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
container-registry.oracle.com/database/enterprise 19.19.0.0 979416fa8cea 3 months ago 6.21GB
Create the Oracle DB container by running:
docker container create -it --name oracle-demo -p 1521:1521 -e ORACLE_PWD=s3cr3t container-registry.oracle.com/database/enterprise:19.19.0.0
Explanation of the command:
docker container create `
-it ` # Run the container in interactive mode
--name [container-name] ` # Name of the container
-p [host-port]:1521 ` # Map the port from host to container for DB
-e ORACLE_PWD=[custom-pass] ` # Password for default user
container-registry.oracle.com/database/express:[version] # Image
Once the container is created, we can use the docker start [container-name] or docker stop [container-name] command to start or stop the container.
docker start oracle-demo
Check if the container has started successfully:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
21d86a9b34e1 container-registry.oracle.com/database/enterprise:19.19.0.0 "/bin/bash -c 'exec …" 6 days ago Up 5 days (healthy) 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp oracle-demo
Some useful commands for troubleshooting
$ docker ps
$ docker logs -f [container-name]
Oracle Instant Client is a free Oracle database client, which is needed for management tasks and ODBC.
Download the Oracle Instant Client Basic, SQL*Plus and ODBC RPM files.
For Ubuntu, Debian and distribution that do not support .rpm file format, convert them into .deb packages using alien. If alien is not installed, run: sudo apt-get install alien.
$ sudo alien instantclient-odbc-linux-arm64.rpm
$ sudo alien instantclient-basic-linux-arm64.rpm
$ sudo alien instantclient-sqlplus-linux-arm64.rpm
After running these commands, .deb files will be generated. Install them:
$ sudo apt install ./oracle-instantclient19.19-odbc_19.19.0.0.0-2_arm64.deb
$ sudo apt install ./oracle-instantclient19.19-basic_19.19.0.0.0-2_arm64.deb
$ sudo apt install ./oracle-instantclient19.19-sqlplus_19.19.0.0.0-2_arm64.deb
If SQL*Plus complains of a missing libaio.so.1 file fix it by running:
$ sudo apt-get install libaio1
If Oracle applications, such as SQL*Plus, are complaining about missing libraries, add the Oracle libraries to the LD_LIBRARY_PATH each time it is used:
$ export LD_LIBRARY_PATH=/usr/lib/oracle/<version>/client(64)/lib/${LD_LIBRARY_PATH:+:$LD_LIBRARY_PATH}
To make this export effective, run:
$ sudo ldconfig
Exec into the database container:
$ docker exec -it oracle-demo bash -c "source /home/oracle/.bashrc; sqlplus /nolog"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 24 13:23:49 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
SQL>
After getting SQL> promt add a poco sample user:
SQL> connect sys as sysdba;
SQL> alter session set "_ORACLE_SCRIPT"=true;
SQL> create user poco identified by poco;
SQL> GRANT ALL PRIVILEGES TO poco;
Verify if the installation and setup are successful by connecting to the database
$ sqlplus poco/poco@localhost:1521/ORCLCDB
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 24 15:30:07 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Mon Oct 23 2023 15:31:55 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL>
Detailed instruction on how to download ODBC Driver can be found on Microsoft Learn, but we will cover the installation here as well.
To be able to download files from Microsoft install curl and gnupg2 packages:
$ sudo apt install curl
$ sudo apt-get install -y gnupg2
To install ODBC Driver for SQL Server on Debian and Ubuntu run following commands
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version
#Debian 9
curl https://packages.microsoft.com/config/debian/9/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
#Debian 10
curl https://packages.microsoft.com/config/debian/10/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
#Debian 11
curl https://packages.microsoft.com/config/debian/11/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
#Debian 12
curl https://packages.microsoft.com/config/debian/12/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
For Debian 11 and below, run the following command.
curl -sSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/microsoft.gpg > /dev/null
For Debian 12 and above, run the following command.
curl -sSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor | sudo tee /usr/share/keyrings/microsoft-prod.gpg > /dev/null
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
if ! [[ "16.04 18.04 20.04 22.04" == *"$(lsb_release -rs)"* ]];
then
echo "Ubuntu $(lsb_release -rs) is not currently supported.";
exit;
fi
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo apt-get install -y unixodbc-dev
ODBC is configured using /etc/odbcinst.ini configuration file. In this file path's to the Oracle and SQL Server driver must be specifed.
SQL Server driver is usually located in /opt/microsoft/msodbcsql<version>/lib64/ with the filename libmsodbcsql-xx.xx.so.x.x
Oracle DB driver file libsqora.so.xx.x can be located by running
dpkg -L oracle-instantclient19.19-odbc
/.
/usr
/usr/lib
/usr/lib/.build-id
/usr/lib/.build-id/a2
/usr/lib/oracle
/usr/lib/oracle/19.19
/usr/lib/oracle/19.19/client64
/usr/lib/oracle/19.19/client64/bin
/usr/lib/oracle/19.19/client64/bin/odbc_update_ini.sh
/usr/lib/oracle/19.19/client64/lib
/usr/lib/oracle/19.19/client64/lib/libsqora.so.19.1
/usr/share
/usr/share/doc
/usr/share/doc/oracle-instantclient19.19-odbc
/usr/share/doc/oracle-instantclient19.19-odbc/changelog.Debian.gz
/usr/share/doc/oracle-instantclient19.19-odbc/copyright
/usr/share/oracle
/usr/share/oracle/19.19
/usr/share/oracle/19.19/client64
/usr/share/oracle/19.19/client64/doc
/usr/share/oracle/19.19/client64/doc/ODBC_LICENSE
/usr/share/oracle/19.19/client64/doc/ODBC_README
/usr/lib/.build-id/a2/faa826bff6b2905bd4a1d0b779a425c5929a94
Now we can configure ODBC connections by editing /etc/odbcinst.ini file.
Edit file by running:
sudo nano /etc/odbcinst.ini
If file does not exists, create it by running:
sudo touch /etc/odbcinst.ini
Content of the file should contain block for Oracle and for SQL Server. Block name in [] is defined by user and will be used later in connection strings. Block should contain path to the odbc driver Driver and short description Description. Example of the file is as follows:
$ cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1
[Oracle 19 ODBC driver]
Description = Oracle ODBC driver for Oracle 19
Driver = /usr/lib/oracle/19.19/client64/lib/libsqora.so.19.1
For Troubleshooting enable ODBC tracing by adding these two lines to the /etc/odbcinst.ini:
(Make sure to remove these lines after development - tracing usually creates quite big files.)
[ODBC]
Trace=Yes
TraceFile=/tmp/odbctrace.log
To configure DSN connection, add configuration to /etc/odbc.ini by running:
sudo nano /etc/odbc.ini
If file does not exists, create it by running:
sudo touch /etc/odbc.ini
The content of the file should contain the name of the Driver configured in /etc/odbcinst.ini, followed by Server Name, Port, Service Name, UserID, and Password. An example of the file is as follows:
$ cat /etc/odbc.ini
[OracleDB]
Description = Oracle ODBC DSN
Driver = Oracle 19 ODBC driver
ServerName = localhost
Port = 1521
ServiceName = ORCLCDB
UserID = poco
Password = poco
To test if the ODBC connection works, run Poco Oracle or SQL Server ODBC tests. If everything works, the output should look like this
macchina.io/platform/Data/ODBC/testsuite$ ./bin/Linux/aarch64/testrunner -all
Driver found: Oracle 19 ODBC driver (Description=Oracle ODBC driver for Oracle 19)
1 DSNs found, enumerating ...
Connecting to [DRIVER={Oracle 19 ODBC driver};DBQ=localhost:1521/ORCLCDB;UID=poco;PWD=poco;TLO=O;FBS=60000;FWC=F;CSR=F;MDI=T;MTS=F;DPM=F;NUM=NLS;BAM=IfAllSuccessful;BTD=F;RST=T;LOB=T;FDL=0;FRC=0;QTO=T;FEN=F;XSM=Default;EXC=F;APA=T;DBA=W;]
*** Connected to [Oracle 19 ODBC driver] test database.