Building access via dblink to three SQL Server databases.
We will use a native technology of the Oracle database, the Heterogeneous Services (HS).
Databases:
10.16.0.11 - Source Database Oracle database
10.16.0.15 - SQL Server target database
Create a user with the necessary read permissions in the SQL Server environment:
User: USR_CONSULTA
Snhea: P!!2012300
Use some Oracle/EPEL repository:
[root@instance-20220613-1754 yum.repos.d]# cat oracle-linux-ol7.repo
[ol7_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=https://yum.oracle.com/repo/OracleLinux/OL7/latest/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
We can use the Microsoft repository too:
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
Installing the necessary packages:
yum clean all
yum repolist
yum install unixODBC unixODBC-devel freetds -y
ODBC installation validation:
[root@olaria ~]# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@olaria ~]#
Creating the configuration init of Heterogeneous Services in Oracle referring to the three SQL Server databases that we will access via dblink:
Name of the databases that we will access in SQL Server: AEQUUS,FINANCIAL,CONTABILIS
[oracle@olaria ~]$ cd $ORACLE_HOME/hs/admin/
[oracle@olaria admin]$ cat initAEQUUS.ora
HS_FDS_CONNECT_INFO = AEQUUS
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini
[oracle@olaria admin]$ cat initFINANCIAL.ora
HS_FDS_CONNECT_INFO = FINANCIAL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini
[oracle@olaria admin]$ cat initCONTABILIS.ora
HS_FDS_CONNECT_INFO = CONTABILIS
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini
Create if it doesn’t exist or edit the ODBC configuration file that we installed earlier, each bank must have a connection entry:
[root@olaria yum.repos.d]# cat /etc/odbc.ini
[DBC Data Sources]
AEQUUS = MSSQL Server
[AEQUUS]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 10.16.0.15
Database = AEQUUS
Port = 1433
TDS_Version = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
DisguiseGuid = Yes
text size = 64512
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
[ODBC Data Sources]
CONTABILIS = MSSQL Server
[CONTABILIS]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 10.16.0.15
Database = CONTABILIS
Port = 1433
TDS_Version = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
text size = 64512
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
[ODBC Data Sources]
FINANCIAL = MSSQL Server
[FINANCIAL]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 10.16.0.15
Database = FINANCIAL
Port = 1433
TDS_Version = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
text size = 64512
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
Create if it doesn’t exist or edit the ODBC configuration file that we installed earlier, each bank must have a connection entry:
[root@olaria yum.repos.d]# cat /etc/odbc.ini
[DBC Data Sources]
AEQUUS = MSSQL Server
[AEQUUS]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 10.16.0.15
Database = AEQUUS
Port = 1433
TDS_Version = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
DisguiseGuid = Yes
text size = 64512
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
[ODBC Data Sources]
CONTABILIS = MSSQL Server
[CONTABILIS]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 10.16.0.15
Database = CONTABILIS
Port = 1433
TDS_Version = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
text size = 64512
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
[ODBC Data Sources]
FINANCIAL = MSSQL Server
[FINANCIAL]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 10.16.0.15
Database = FINANCIAL
Port = 1433
TDS_Version = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
text size = 64512
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
Adjust tnsnames.ora with the access entries to the three SQL Server databases that we will use in dblink:
[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
AEQUUS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
(CONNECT_DATA=(SID=AEQUUS))
(HS=OK)
)
CONTABILIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
(CONNECT_DATA=(SID=CONTABILIS))
(HS=OK)
)
FINANCIAL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
(CONNECT_DATA=(SID=FINANCIAL))
(HS=OK)
)
Adjust the listener.ora with the access entries to the three SQL Server databases that we will use in the dblink:
[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = olaria)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
DEFAULT_SERVICE_LISTENER=orcl
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
(SID_DESC=
(SID_NAME=AEQUUS)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
(PROGRAM=dg4odbc)
)
(SID_DESC=
(SID_NAME=CONTABILIS)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
(PROGRAM=dg4odbc)
)
(SID_DESC=
(SID_NAME=FINANCIAL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
(PROGRAM=dg4odbc)
)
)
- Reload listener.ora settings
[oracle@olaria db_1]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUN-2022 10:16:12
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=olaria)(PORT=1521)))
The command completed successfully
Test the access via odbc by the osql utility:
osql -S AEQUUS -U USR_CONSULTA -P 12398546
Attempting connection as dblinkdev ...
+ isql AEQUUS USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
osql -S CONTABILIS -U USR_CONSULTA -P 12398546
Attempting connection as dblinkdev ...
+ isql CONTABILIS USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
osql -S FINANCIAL -U USR_CONSULTA -P 12398546
Attempting connection as dblinkdev ...
+ isql FINANCIAL USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Create dblinks and perform access tests:
create public database link AEQUUS connect to "USR_CONSULTA" identified by "12398546" using 'AEQUUS';
create public database link CONTABILIS connect to "USR_CONSULTA" identified by "12398546" using 'CONTABILIS';
create public database link FINANCIALARACAJU connect to "USR_CONSULTA" identified by "12398546" using 'FINANCIALARACAJU';
Test select dblinks:
select "codigo","nome" from IMPACOES@AEQUUS;
select * from ALVARA@CONTABILIS;
select "nu_cnpj","sq_produto","nm_produto" from PRODUTO@FINANCIAL;
Note: If any SQL Server database has tables with the varchar(max) data type, we must create views to consume this data, because the varchar(max) data type is problematic with dblink, I could not get around this problem,
this way I created a view, see the example below:
CREATE VIEW VEW_IMPACOES
AS
SELECT codigo,CONVERT(VARCHAR(2000),nome) nome FROM IMPACOES;
