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>