3 Replies Latest reply: Jan 14, 2014 11:37 PM by kgronau RSS

Another request for Oracle to PostgreSQL connectivity help

user634615 Newbie
Currently Being Moderated

Like others who have come here support, I also am having problems getting a database link in Oracle to successfully connect to a PosgreSQL database.  I can successfully isql to the PostgreSQL database from the Oracle server.

 

Background

Oracle installed on 64 bit version of Centos 6.2

PostgreSQL database also on a 64 bit version of Centos 6.2

 

Contents of odbcinst.ini:

# Example driver definitions

 

# Driver from the postgresql-odbc package

# Setup from the unixODBC package

[PostgreSQL]

Description    = ODBC for PostgreSQL

Driver        = /usr/lib/psqlodbc.so

Setup        = /usr/lib/libodbcpsqlS.so

Driver64    = /usr/lib64/psqlodbc.so

Setup64        = /usr/lib64/libodbcpsqlS.so

FileUsage    = 1

 

[ODBC]

Trace = yes

Tracefile = /u01/oracle/odbc_errors/odbc.log

 

Contents of .odbc.ini:

[dg4odbc]

Driver = PostgreSQL

#Driver = /usr/lib64/psqlodbc.so

DATABASE = p316

PORT = 5432

#SERVER = 10.34.160.10

ServerName = 10.34.160.10

UID = sa

PWD = amdocs

#charset = utf8

#ConnSettings =SET CLIENT_ENCODING to 'UNICODE'

 

results of odbcinst -j command:

unixODBC 2.2.14

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /u01/oracle/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

 

contents of tnsnames.ora:

dg4odbc = (Description =

            (ADDRESS_LIST =

              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

            ) 

          (CONNECT_DATA = (SID = dg4odbc))

          (HS = OK)

        )

 

 

contents of listener.ora:

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/oracle/prod/db_11.2.0.3)

      (SID_NAME = dg4odbc)

      (PROGRAM = dg4odbc)

      (ENV ="LD_LIBRARY_PATH=/usr/lib64:/usr/local/lib:/usr/lib:$ORACLE_HOME/lib:$ORACLE_HOME/hs/lib")

    )

  )

 

contents of initdg4odbc.ora (gateway init.ora file):

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

 

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = dg4odbc

#HS_FDS_TRACE_LEVEL = 255

HS_FDS_TRACE_LEVEL = DEBUG

#HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

#HS_LANGUAGE = american_america.utf8

HS_LANGUAGE = american_america.we8msin1252

#HS_NLS_NCHAR = UCS2

 

#

# ODBC specific environment variables

#

set ODBCINI=/u01/oracle/.odbc.ini

set ODBCINST=/etc/odbcinst.ini

 

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

Error from gateway trace file:

Entered hgopoer at 2014/01/14-16:57:42

hgopoer, line 231: got native error 202 and sqlstate 0; message follows...

c

Exiting hgopoer, rc=0 at 2014/01/14-16:57:42

hgocont, line 2754: calling SqlDriverConnect got sqlstate 0

Exiting hgocont, rc=28500 at 2014/01/14-16:57:42 with error ptr FILE:hgocont.c LINE:2774 FUNCTION:hgocont() ID:Something other than invalid authorization

Exiting hgolgon, rc=28500 at 2014/01/14-16:57:42 with error ptr FILE:hgolgon.c LINE:801 FUNCTION:hgolgon() ID:Calling hgocont

Entered hgoexit at 2014/01/14-16:57:42

Exiting hgoexit, rc=0

 

I can get an ODBC trace file to generate when i use the isql command.  However, I can't seem to generate one when I try accessing the PostgreSQL database from Oracle.  Does anyone have an idea as to how I can get and ODBC trace to generate?  In the .odbc.ini file under the [ODBC] heading I tried setting Trace=1 and TraceFile=/path/of /file but I didn't know what to set for the TraceDll setting.

 

Thanks for your patience with the length of this posting.

L Jacob

  • 1. Re: Another request for Oracle to PostgreSQL connectivity help
    kgronau Guru
    Currently Being Moderated

    The PostGreSQL ODBC driver does not contain generic ODBC functions. So you need to use a driver manager in your gateway init file referenced by HS_FDS_SHAREABLE_NAME.

    So remove

    HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

    on Unix a common ODBC driver is unixODBC and its library is called libodbc just as you had it in the commented line. But please make sure that the ODBC driver is a 64bit ODBC driver as your ODBC driver itself is a 64bit ODBC driver library and DG4ODBC on 64bit OS is also 64bit.

     

    So check if /usr/local/lib/libodbc.so is a 64bit lib, else install it from UnixODBC.org - then set correctly: HS_FDS_SHAREABLE_NAME = <path>/libodbc.so

     

    - Klaus

  • 2. Re: Another request for Oracle to PostgreSQL connectivity help
    user634615 Newbie
    Currently Being Moderated

    Thank you.  That helped a lot.  After making the change, plus changing the value of  HS_LANGUAGE to american_america.we8iso8859P1 I was able to successfully select from the PostgreSQL database.

     

    I'm now determined to learn the difference between an odbc driver and an odbc driver manager.

     

    I have one parting question if you don't mind.  I know why I had to set the HS_LANGUAGE setting to a Western European.  I ended up with the same problem that you have replied to others about.  The PostgreSQL database is actually a UTF8 database.  Are you saying that we can't retrieve the non-Western European characters with this setup; or, is there an additional setting I can use in the gateway init file to allow that?

    L Jacob

  • 3. Re: Another request for Oracle to PostgreSQL connectivity help
    kgronau Guru
    Currently Being Moderated

    Let me start with the difference between ODBC Driver and Driver Manager.

     

    The ODBC driver MANAGER is responsible for generic ODBC functions and it knows how to interpret the odbc.ini. In addition it is loading the real ODBC driver. The driver manager is generic and required for almost all drivers.The ODBC driver itself is designed for a dedicated database and very often shipped by the database vendor.

    On Windows for example Microsoft ships the driver manager and you only need to install the ODBC driver.

     

    Regarding your UTF-8 Postgres database. It depends on the ODBC Driver Manager version. Older ODBC Driver Managers from unixODBC expect the ODBC connect string in double byte when you specify an UTF8 charset. As all applications send the connect string in single bytes the older driver managers fails and doesn't load the ODBC driver.

    Newer driver managers (2.3 onwards) are working correctly. So when you want to use UTF8 please make sure you have a 2.3.x ODBC Driver manager installed.

    The release installed in your env can be checked using "odbcinst" -j after setting the PATH and LD_LIBRARY_PATH correctly.

     

    - Klaus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points