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 nulltest_name | character
varying(32)|
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324