By Mouhamadou Diaw

Sometimes you need to access to your SQLServer from your Oracle database. There is many ways and tools to do this. In this blog I am describing how to perform this task using ODBC.
The configuration is below
-Oracle Linux server : serverora 192.168.56.41 with Oracle 19 database orcl
-Windows server : winrac1 192.168.56.100 with SQLServer Express

On my Windows server, I have a database named testdb and a table named article.

The first step is to verify that the file dg4odbc is present in your ORACLE_HOME environment

1
2
3
4
5
[oracle@serverora ~]$ cd $ORACLE_HOME/bin
[oracle@serverora bin]$ file dg4odbc
dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=11d0b16445c4b65545b285a222628e03c99e8900, not stripped
[oracle@serverora bin]$

After this we do install the ODBC driver for SQLServer on our Oracle server
-Install the repository

1
2
3
4
5
[root@serverora yum.repos.d]# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   193  100   193    0     0    265      0 --:--:-- --:--:-- --:--:--   265
[root@serverora yum.repos.d]#

-Search the name of the package

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@serverora yum.repos.d]# yum search msodbc
Loaded plugins: ulninfo
epel/x86_64/metalink                                                                         |  19 kB  00:00:00
epel                                                                                         | 4.7 kB  00:00:00
ol7_UEKR5                                                                                    | 3.0 kB  00:00:00
ol7_latest                                                                                   | 3.6 kB  00:00:00
...
...
=============================================== N/S matched: msodbc ================================================
msodbcsql.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
msodbcsql17.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
  Name and summary matches only, use "search all" for everything.

-Install the package msodbcsql17
You can find more info in the documentation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
[root@serverora yum.repos.d]# yum remove unixODBC-utf16 unixODBC-utf16-devel
Loaded plugins: ulninfo
No Match for argument: unixODBC-utf16
No Match for argument: unixODBC-utf16-devel
No Packages marked for removal
[root@serverora yum.repos.d]# ACCEPT_EULA=Y yum install -y msodbcsql17
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.8.1.1-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql17-17.8.1.1-1.x86_64
--> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql17-17.8.1.1-1.x86_64
--> Running transaction check
---> Package unixODBC.x86_64 0:2.3.7-1.rh will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================
 Package                 Arch               Version                   Repository                               Size
====================================================================================================================
Installing:
 msodbcsql17             x86_64             17.8.1.1-1                packages-microsoft-com-prod             905 k
Installing for dependencies:
 unixODBC                x86_64             2.3.7-1.rh                packages-microsoft-com-prod             213 k
Transaction Summary
====================================================================================================================
Install  1 Package (+1 Dependent package)
Total download size: 1.1 M
Installed size: 2.9 M
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/packages-microsoft-com-prod/packages/unixODBC-2.3.7-1.rh.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY
Public key for unixODBC-2.3.7-1.rh.x86_64.rpm is not installed
(1/2): unixODBC-2.3.7-1.rh.x86_64.rpm                                                        | 213 kB  00:00:00
(2/2): msodbcsql17-17.8.1.1-1.x86_64.rpm                                                     | 905 kB  00:00:00
--------------------------------------------------------------------------------------------------------------------
Total                                                                               1.9 MB/s | 1.1 MB  00:00:00
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
 Userid     : "Microsoft (Release signing) "
 Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
 From       : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : unixODBC-2.3.7-1.rh.x86_64                                                                       1/2
  Installing : msodbcsql17-17.8.1.1-1.x86_64                                                                    2/2
  Verifying  : msodbcsql17-17.8.1.1-1.x86_64                                                                    1/2
  Verifying  : unixODBC-2.3.7-1.rh.x86_64                                                                       2/2
Installed:
  msodbcsql17.x86_64 0:17.8.1.1-1
Dependency Installed:
  unixODBC.x86_64 0:2.3.7-1.rh
Complete!
[root@serverora yum.repos.d]#

-Install the package mssql-tools

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@serverora yum.repos.d]# ACCEPT_EULA=Y yum install -y mssql-tools
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.8.1.1-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================
 Package                 Arch               Version                   Repository                               Size
====================================================================================================================
Installing:
 mssql-tools             x86_64             17.8.1.1-1                packages-microsoft-com-prod             287 k
Transaction Summary
====================================================================================================================
Install  1 Package
Total download size: 287 k
Installed size: 714 k
Downloading packages:
mssql-tools-17.8.1.1-1.x86_64.rpm                                                            | 287 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-tools-17.8.1.1-1.x86_64                                                                    1/1
  Verifying  : mssql-tools-17.8.1.1-1.x86_64                                                                    1/1
Installed:
  mssql-tools.x86_64 0:17.8.1.1-1
Complete!
[root@serverora yum.repos.d]#

-Install the unixODBC-devel package

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@serverora bin]# yum install -y unixODBC-devel
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package unixODBC-devel.x86_64 0:2.3.7-1.rh will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================
 Package                    Arch               Version                Repository                               Size
====================================================================================================================
Installing:
 unixODBC-devel             x86_64             2.3.7-1.rh             packages-microsoft-com-prod              42 k
Transaction Summary
====================================================================================================================
Install  1 Package
Total download size: 42 k
Installed size: 196 k
Downloading packages:
unixODBC-devel-2.3.7-1.rh.x86_64.rpm                                                         |  42 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : unixODBC-devel-2.3.7-1.rh.x86_64                                                                 1/1
  Verifying  : unixODBC-devel-2.3.7-1.rh.x86_64                                                                 1/1
Installed:
  unixODBC-devel.x86_64 0:2.3.7-1.rh
Complete!
[root@serverora bin]#

Now we can edit the odbc files. Below the contents of my files
-/etc/odbc.ini

1
2
3
4
5
6
7
8
[root@serverora etc]# cat odbc.ini
[MY_ODBC_TEST]
Driver = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
Server = winrac1
Port = 1433
Database = testdb
[root@serverora etc]#

-/etc/odbcinst.ini

1
2
3
4
5
6
7
[root@serverora etc]# cat odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
UsageCount=1
[root@serverora etc]#

Be sure that the Driver is pointing to the right location for libmsodbcsql-17.8.so.1.1

1
2
3
[oracle@serverora lib64]$ ls -l /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
-rwxr-xr-x. 1 root root 2065184 Jun 26 12:34 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
[oracle@serverora lib64]$

Now let’s validate the connexion ODBC to the SQLserver database from the Oracle server

-Using sqlcmd

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@delphixdev1 ~]$ /opt/mssql-tools/bin/sqlcmd -D -S MY_ODBC_TEST -U sa
Password:
1> select * from article;
2> go
idart  designation
------ --------------------------------------------------
     1 boisson
     2 écran
     3 où
(3 rows affected)
1>

-Using isql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[oracle@serverora opt]$ which isql
/usr/bin/isql
[oracle@serverora ~]$ isql -v MY_ODBC_TEST sa root
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from article;
+-------+---------------------------------------------------+
| idart | designation                                       |
+-------+---------------------------------------------------+
| 1     | boisson                                           |
| 2     | écran                                            |
| 3     | où                                               |
+-------+---------------------------------------------------+
SQLRowCount returns 0
3 rows fetched
SQL>

We can see above that I can query my article table in the SQLServer database from my Oracle server.

On the Oracle server let’s create the file inithsconnect.ora for the instance hsconnect (you can choose another name for the instance if you want)

1
2
3
4
5
6
7
8
9
10
11
[oracle@serverora admin]$ pwd
/u01/app/oracle/product/19.0.0/db_1/hs/admin
[oracle@serverora admin]$ cat inithsconnect.ora
HS_FDS_CONNECT_INFO = MY_ODBC_TEST
HS_FDS_TRACE_LEVEL = user
HS_FDS_SHAREABLE_NAME = /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1
set ODBCINI = /etc/odbc.ini
HS_NLS_NCHAR = UCS2
[oracle@serverora admin]$

On the Oracle server let’s adjust the listener.ora to add a static entry for the instance hsconnect

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[oracle@serverora admin]$ pwd
/u01/app/oracle/product/19.0.0/db_1/network/admin
[oracle@serverora admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.41)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=hsconnect)
     (ORACLE_HOME= /u01/app/oracle/product/19.0.0/db_1)
     (PROGRAM=dg4odbc)
     (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql17/lib64)
   )
 )
[oracle@serverora admin]$

On the Oracle server let’s add an entry for instance hsconnect

1
2
3
4
5
6
7
hsconnect=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.41)(PORT=1521))
   (CONNECT_DATA= (SID=hsconnect))
   (HS=OK)
   )
 )

Be sure that you can do a tnsping to your hsconnect alias

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[oracle@serverora admin]$ tnsping  hsconnect
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-OCT-2021 12:33:50
Copyright (c) 1997, 2020, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.41)(PORT=1521)) (CONNECT_DATA= (SID=hsconnect)) (HS=OK))
OK (0 msec)
[oracle@serverora admin]$

From your Oracle database, create a dblink pointing to your SQLServer and test your dblink

1
2
3
4
5
6
7
8
9
SQL> create public database link hslink connect to "sa" identified by "root" using 'hsconnect';
Database link created.
SQL>  select * from dual@hslink;
D
-
X

And then finally you should be able to query your SQLServer article table from your Oracle database

1
2
3
4
5
6
7
SQL> select * from article@hslink;
     idart designation
---------- -----------------------------------
         1 boisson
         2 ecran
         3 ou

To resolve the issue with the French accents, just set the NLS_LANG as below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[oracle@serverora admin]$ export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@serverora admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 29 13:15:11 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> col designation for a25
SQL> select * from article@hslink;
     idart designation
---------- -------------------------
         1 boisson
         2 écran
         3 où
SQL>

Conclusion

Hope this article will help

Post Views: 3,767
Thumbnail [60x60]
by
Oracle Team