Skip site navigation (1) Skip section navigation (2)

Re: Issue in Mapping varchar datatype of Postgre to Oracle

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: "vidisha(dot)shah(at)tcs(dot)com" <vidisha(dot)shah(at)tcs(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Issue in Mapping varchar datatype of Postgre to Oracle
Date: 2006-03-29 16:29:05
Message-ID: 36e682920603290829h654dda19o65f34a4ceab62724@mail.gmail.com (view raw or flat)
Thread:
Lists: 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 null
 test_name | character varying(32) |

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

In response to

pgsql-hackers by date

Next:From: Jonah H. HarrisDate: 2006-03-29 16:45:43
Subject: Re: Issue on Varchar Ordering
Previous:From: Ludek FinstrleDate: 2006-03-29 15:47:54
Subject: Re: [ODBC] Unable to Query the datatype varchar of PostgreSQL Server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group