Oracle 11g通过dg4odbc配置dblink连接PostgreSQL

1、安装unixodbc

2、安装postgresql 

yum install -y postgresql17-odbc

3、配置postgresqlodbc数据源 

vim  /usr/local/etc/odbcinst.ini

##添加如下
[mypg]
Driver = /usr/lib64/psqlodbcw.so
Servername = localhost
Port = 5432
Database = postgres
Username = postgres
Password = password
SocketBufferSize = 4096
FetchBufferSize = 100

4、测试odbc连通性

5、配置透明网关、监听文件以及对应编写的hsodbc的ora文件，我这里是mypg.ora

##对应编写的hsodbc的ora文件
vim $ORACLE_HOME/hs/admin/mypg.ora
##添加如下
HS_FDS_CONNECT_INFO = mypg17
HS_FDS_TRACE_LEVEL = debug
set ODBCINI= /usr/local/etc/odbc.ini
#HS_FDS_SHAREABLE_NAME = /usr/local/psqlodbc/psqlodbcw.so(已注释，使用的psqlodbc驱动未调通，应该是yum默认安装的postgresql-odbc驱动版本有点低，本次使用unixodc驱动)
HS_NLS_NCHAR = UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
#NLS_LANG=AMERICAN_AMERICA.AL32UTF8
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so


##配置oracle静态监听
vim $ORACLE_HOME/network/admin/listener.ora
##添加如下
SID_LIST_LISTENER=
  (SID_LIST=
     (SID_DESC=
        (PROGRAM = dg4odbc)
        (ORACLE_HOME = /opt/oracle/app/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = mypg)
      )
  )
USE_SID_AS_SERVICE_listener=on

##配置tnsnames.ora
vim $ORACLE_HOME/network/admin/tnsnames.ora
##添加如下
mypg=
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
         (SID = mypg)   
      )
      (HS = OK)
   )

6、重启监听

##oracle用户登录的shell下执行重启监听的命令，重启之后你会发现有一个mypg的实例被监听，状态为unknown，不用管它，具体还是要看测试查询的结果
##停止监听
lsnrctl stop
##启动监听 
lsnrctl start
##查看监听状态 
lsnrctl status

7、dblink测试

##postgresql创建表、插入数据
##在Oracle中创建连接postgresql的dblink
create database link mypg_test connect to "postgres" identified by "password" using 'mypg';
##测试在Oracle通过dblink查看postgresql的数据
select * from "public"."t1"@mypg_test;

postgresql

oracle通过dblink查询postgresql数据 

如果查询中文乱码，需要设置字符集环境变量，就OK！

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8