如何使用Oracle ODBC网关通过ODBC将Oracle连接到PostgreSQL?
问题描述:
我想从运行Oracle自定义Linux风格的服务器上的Oracle实例运行openSUSE的服务器上的PostgreSQL实例中进行选择。我想使用Oracle ODBC网关执行此操作。过去我成功完成了这个任务,并且继续使用相同的Oracle框和其他SUSE/Postgres框来完成此操作。如何使用Oracle ODBC网关通过ODBC将Oracle连接到PostgreSQL?
在SUSE我的ODBC管理器(Postgres的)侧是:unixODBC的
我ODBC.INI是:
[postgresql]
Description = Test to Postgres
Driver = /usr/lib64/psqlodbcw.so
Trace = Yes
TraceFile = sql.log
Database = host
Servername = localhost
UserName = *****
Password = *****
Port = 5432
Protocol =
ReadOnly = Yes
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
我ODBCINST.INI是
[postgresql]
Description = Postgresql driver for Linux
Driver = /usr/lib64/psqlodbcw.so
UsageCount = 1
我的tnsnames.ora (省略其他ODBC项)是:
# tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ODBC_SERVER123=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=dbs1)
(PORT=1522)
)
(CONNECT_DATA=
(SID=server123)
)
(HS=OK)
)
我的listener.ora(省略其他SID_DESC项)为:
# listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522))
)
)
)
ADR_BASE_LISTENER = /opt/oracle
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=server123)
(ORACLE_HOME=/opt/oracle/product/11gR1/db)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/lib64:/opt/oracle/product/11gR1/db/lib)
)
)
TRACE_LEVEL_LISTENER = 0
LOGGING_LISTENER = off
而且,这里是位于$ ORACLE_HOME/HS /管理的inithost123.ora文件:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = server123
HS_FDS_TRACE_LEVEL = 0
#HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_LANGUAGE=AMERICAN_AMERICA.US7ASCII
HS_FDS_TIMESTAMP_MAPPING = "TIMESTAMP(6)"
HS_FDS_FETCH_ROWS=1
HS_FDS_SQLLEN_INTERPRETATION=32
#
# ODBC specific environment variables
#
set ODBCINI=/etc/unixODBC/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
和公正的好措施,我的sqlnet.ora是:
:# sqlnet.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /opt/oracle
我用下面加一个网关连接oracle的方
CREATE DATABASE LINK ODBC_SERVER123 CONNECT TO "*****" IDENTIFIED BY "*****" USING 'ODBC_SERVER123';
当我尝试执行我的选择我收到以下错误:
[SQL] select * from "legit_view"@ODBC_SERVER123
[Err] ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Data source name not found, and no default driver specified {IM002}
ORA-02063: preceding 2 lines from ODBC_SERVER123
我能成功地测试ODBC本地的SUSE/Postgres的盒子ISQL:
>isql -v postgresql ***** *****
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
哪一步有我忘记了?
答
您的odbc.ini文件必须位于远程盒子上!也就是说,发起查询的那个人。在上面的例子中就是Oracle盒子。这是一个必须在Oracle(远程)框中输入的条目:
[server123]
Driver = /usr/lib64/psqlodbcw.so
Description = ODBC to PostgreSQL on Server 123
Trace = No
Tracefile = /var/log/sql_host_123.log
Servername = ip.address.for.123
Username = *****
Password = *****
Port = 5432
Protocol = 6.4
Database = host
QuotedId = Yes
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
两件事。 1.我认为驱动程序应该是odbc.ini(odbcinst.ini中的标题)中的“postgresql”,并且2.可以使用命令行工具“isql”来测试您的unixodbc连接。 – 2013-02-22 18:07:35