2022 September
DBlink Oracle for three databases in SQL Server
Category: Database Author: César Carvalho Date: 2 years ago Comments: 0

DBlink Oracle for three databases in SQL Server

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;

 

 

 
César Carvalho – DBA
Contact: https://twitter.com/Cesar_DBA
https://sgbdbrasil.wordpress.com/