Configure odbc transparent gateway for oracle Access to IPVs DB

Recently, we helped customers configure the odbc transparent gateway to access IPVs db in oracle through DB link.

A simple record:

(1) configuration of listener. ora and tnsnames. ora:

[Wsj81 @ localhost admin] $ cat listener. ora
# Listener. ora Network Configuration File:/wsj/oracle/app/product/11.2.0/dbhome_1/network/admin/listener. ora
# Generated by Oracle configuration tools.
 
SID_LIST_ORCL =
(SID_LIST =
(SID_DESC =
(SID_NAME = pgdb)
(ORACLE_HOME =/wsj/oracle/app/product/11.2.0/dbhome_1)
(ENV = "LD_LIBRARY_PATH =/usr/lib64:/wsj/oracle/app/product/11.2.0/dbhome_1/lib: usr/local/lib ")
(PROGRAM = dg4odbc)
    )
  )
 
ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 19.89.64.111) (PORT = 1531 ))
      )
    )
  )
 
 
[Wsj81 @ localhost admin] $
[Wsj81 @ localhost admin] $ cat tnsnames. ora
Pgdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp) (HOST = 19.89.64.111) (PORT = 1521 ))
(CONNECT_DATA = (SID = pgdb ))
(HS = OK)
  )

(2) transparent gateway configuration. The two files are under $ ORACLE_HOME/hs/admin.

[Wsj81 @ localhost admin] $ cat odbc. ini
[Pgdb]
Driver =/usr/local/lib/psqlodbcw. so
Setup =/usr/lib64/libodbc. so
Description = POSTGRESQL
Servername = 88.23.19.121
Port = 5434
Protocl = 6.4
FetchBufferSize = 99
Username = S
Password = abcd4321
Database = pgdb
ReadOnly = no
Debug = 1
ConnSettings =
[Wsj81 @ localhost admin] $
[Wsj81 @ localhost admin] $
[Wsj81 @ localhost admin] $
[Wsj81 @ localhost admin] $ cat initpgdb. ora
HS_FDS_CONNECT_INFO = pgdb
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc. so
HS_LANGUAGE = AMERICAN_AMERICA.ZHS16GBK
Set ODBCINI =/wsj/oracle/app/product/11.2.0/dbhome_1/hs/admin/odbc. ini
[Wsj81 @ localhost admin] $
Create a db link

SQL> create database link pgdb connect to "S" identified by "abcd4321" using 'pgdb ';
 
Database link created.
 
SQL>

Test the connection. Note that double quotation marks must be added to the table in pg:

SQL> select * from "t1" @ pgdb;
 
No rows selected
 
SQL>