Re: Issue in Mapping varchar datatype of Postgre to Oracle - Mailing list pgsql-hackers

From Jonah H. Harris
Subject Re: Issue in Mapping varchar datatype of Postgre to Oracle
Date
Msg-id 36e682920603290829h654dda19o65f34a4ceab62724@mail.gmail.com
Whole thread Raw
In response to Issue in Mapping varchar datatype of Postgre to Oracle  (vidisha.shah@tcs.com)
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Ludek Finstrle
Date:
Subject: Re: [ODBC] Unable to Query the datatype varchar of PostgreSQL Server
Next
From: "Jonah H. Harris"
Date:
Subject: Re: Issue on Varchar Ordering