{"id":7101,"date":"2022-12-23T16:50:43","date_gmt":"2022-12-23T16:50:43","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/how-to-setup-a-heterogeneous-database-link-using-dg4odbc\/"},"modified":"2026-11-03T19:27:50","modified_gmt":"2026-11-03T19:27:50","slug":"how-to-setup-a-heterogeneous-database-link-using-dg4odbc","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/how-to-setup-a-heterogeneous-database-link-using-dg4odbc\/","title":{"rendered":"How to setup a heterogeneous database link using dg4odbc"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

Nowadays companies use different database technologies for keeping their data and often they need to access data from one data source to another data source. However, as the data is on different database products, it does not work, and this takes up lots of effort on part of the developer to intermix data between different databases.<\/p>\n

\nThis blog discusses how one can access data with cross-platform\/cross-technology databases using the Database Link object. Through the following example, I will explain how to access data between ORACLE and an MS-SQL database using DBLINK. <\/p>\n

_______________________________________________________________________________<\/p>\n

1. You need to first install the ODBC Driver for SQL Server\/MySQL server on the server where you want to create the DB link.<\/p>\n

2. Create the init file in $ORACLE_HOME\/hs\/admin as follows:<\/p>\n

 
\n[oracle@ip_machine admin]$ cat initORADB.ora
\nHS_FDS_CONNECT_INFO = ORADB
\nHS_FDS_SHAREABLE_NAME = \/usr\/lib64\/libodbc.so
\nHS_FDS_TRACE_LEVEL = 0
\nHS_LANGUAGE = AMERICAN_AMERICA.WE8MSWIN1252
\nHS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
\nHS_NLS_LENGTH_SEMANTICS = CHAR
\nHS_NLS_NCHAR = UCS2
\nHS_FDS_PROC_IS_FUNC = TRUE
\nHS_FDS_RESULTSET_SUPPORT = TRUE<\/em><\/p>\n

\n“set LD_LIBRARY_PATH=\/opt\/microsoft\/msodbcsql17\/lib64:\/usr\/lib64:\/apps\/oracle\/product\/12.2.0\/dbhome_1\/lib
\nset ODBCINI=\/apps\/oracle\/product\/12.2.0\/dbhome_1\/hs\/admin\/odbc.ini”
\n <\/p>\n

3. Create the odbc.ini and add the value of driver location \/source server name\/ DB name\/username & password.
\n[oracle@ip_machine admin]$ cat odbc.ini
\n[ODBC Data Sources]
\nORADB=MS SQL Server
\n[ORADB]<\/em><\/p>\n

Driver=\/opt\/microsoft\/msodbcsql17\/lib64\/libmsodbcsql-17.5.so.2.1
\nDescription=Microsoft ODBC Driver 17 for SQL Server
\nServer= tcp:MSSQLSERVER_HOSTNAME.,1433
\nDatabase=IOPS_DATA_Warehouse
\nUser=ORADB
\nPassword=xxxxx
\nQuotedId=YES
\nAnsiNPW=YES
\nThreading=1
\nUsageCount=1
\nAutoTranslate=No<\/em>
\n <\/p>\n

4. Create the listener or add it to the existing listener. In this case, I am creating a new listener and initializing the same. <\/p>\n

MSSQL =
\n (DESCRIPTION_LIST =
\n  (DESCRIPTION =
\n   (ADDRESS = (PROTOCOL = TCP)(HOST = ip_machine)(PORT = 1523))
\n  )
\n )
\nSID_LIST_MSSQL =
\n(SID_LIST =
\n (SID_DESC =
\n  (SID_NAME = ORADB)
\n  (ORACLE_HOME = \/apps\/oracle\/product\/12.2.0\/dbhome_1)
\n  (PROGRAM = \/apps\/oracle\/product\/12.2.0\/dbhome_1\/bin\/dg4odbc)
\n  (ENVS = “LD_LIBRARY_PATH=\/opt\/microsoft\/msodbcsql17\/lib64\/libmsodbcsql-17.5.so.2.1:\/opt\/microsoft\/msodbcsql17\/lib64:\/usr\/lib64:\/apps\/oracle\/product\/12.2.0\/dbhome_1\/lib”)
\n )
\n)<\/em>
\n <\/p>\n

5.  Add the TNS Entry in $ORACLE_HOME\/network\/admin
\n“`
\nORADB =
\n (DESCRIPTION =
\n  (ADDRESS = (PROTOCOL = tcp)(HOST = ip_machine)(PORT = 1523))
\n  (CONNECT_DATA =
\n   (SID = ORADB)
\n  )
\n  (HS = OK)
\n )<\/em>
\n`<\/p>\n

6. Log in to the database and create the public database link.<\/p>\n

Create public database link IOPS_DATA_WAREHOUSE connect to ORADB identified by “xxxx” using ‘ORADB’;
\nDatabase link created.
\nSQL> select * from dual@IOPS_DATA_WAREHOUSE;
\nD
\nX<\/em><\/p>\n

With the above, the database link between cross-database technology is created and tested successfully. <\/p>\n

Conclusion<\/strong><\/p>\n

 Though it takes a bit of an effort to create a Database link between platforms, it makes it very convenient while accessing data between cross-platforms. <\/p>\n","protected":false},"excerpt":{"rendered":"

How to setup a heterogeneous database link using dg4odbc<\/p>\n","protected":false},"author":2,"featured_media":7103,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26256","content_type":"blog","created_by":"10130","authored_on":"2023-11-18T16:50:43+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Jay Pathak, Oracle Database Administrator, Rackspace Technology","seo_description":"Access data between different databases using the Database Link object and learn how to create a heterogeneous database link.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26256-insights_image-database-technical-blog-2-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2022-12-23T16:50:43","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"How to Setup DG4ODBC Database | Rackspace Technology","field_topics":"","field_slug":"how-to-setup-a-heterogeneous-database-link-using-dg4odbc","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7101","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7101"}],"version-history":[{"count":1,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7101\/revisions"}],"predecessor-version":[{"id":7102,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7101\/revisions\/7102"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7103"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}