Here's my configuration which works just fine (using unixODBC). The
postgres database was created UTF-8.
Software:
- SuSE 10
- PostgreSQL 8.1.3
- Oracle10g XE
- psqlODBC 07.03.0260 (make sure to compile --with-unixODBC)
- unixODBC 2.2.11
My /etc/unixODBC/odbcinst.ini:
[PostgreSQL]
Description = enterprisedb
Driver = /usr/local/lib/psqlodbc30w.so
UsageCount = 1
CPTimeout =
My /etc/unixODBC/odbc.ini:
[pgodbc]
Description = PostgreSQL Database (postgres)
Driver = PostgreSQL
Database = postgres
Servername = localhost
UserName =
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
My $ORACLE_HOME/network/admin/listener.ora entry:
(SID_DESC =
(SID_NAME = pgodbc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM = hsodbc)
)
My $ORACLE_HOME/network/admin/tnsnames.ora entry:
# PostgreSQL Database (postgres) Using HSODBC
PGDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = linux)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = pgodbc)
)
(HS=OK)
)
My $ORACLE_HOME/hs/admin/initpgodbc.ora:
HS_FDS_CONNECT_INFO = pgodbc
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_LEVEL = myodbc3.trc
HS_DB_NAME=pgodbc
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
set ODBCINI=/etc/unixODBC/odbc.ini
After all of this is setup, the following works fine in SQL*Plus:
SQL> CREATE PUBLIC DATABASE LINK pgdb CONNECT TO "edb82" IDENTIFIED BY
"edb" USING 'PGDB';
SQL> SELECT "test_name" FROM "test_tbl"@pgdb;
Where test_tbl is:
postgres=# \d test_tbl
Table "public.test_tbl"
Column | Type | Modifiers
-----------+-----------------------+-----------
test_id | numeric(10,0) | not null
test_name | character varying(32) |
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
