This discussion is archived
6 Replies Latest reply: Feb 28, 2012 1:18 AM by 919587 RSS

Create a dblink oracle to postgres with dg4odbc

919587 Newbie
Currently Being Moderated
Hi,

I'm trying to create a dblink from a database Oracle (10.2.0.3) to PostgreSQL database (8.3.11) with DG4ODBC (11.2.0.1.0).

I've installed the postgres driver 64 bit :
file /usr/lib64/psqlodbc.so
/usr/lib64/psqlodbc.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped
I'm configuring the /oracle/.odbc.ini :
[record]
Driver=/usr/lib64/psqlodbc.so
Description  = PostgreSQL ODBC
Servername       = cdxlan035
Port         = 5435
Username         = rec_lct
Password     = rec_lct
Database     = REC_TEST
I'm configuring the /oracle/product/DG4ODBC/hs/admin/initrecord.ora like this :
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = record
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = /oracle/product/DG4ODBC/hs/log/hsodbc.trc
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/oracle/.odbc.ini
I'm configuring the /oracle/product/DG4ODBC/network/admin/listener.ora
LISTENER_HS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cdxlan036.douane)(PORT = 1525))
    )
  )

ADR_BASE_LISTENER_HS = /oracle/product/DG4ODBC

SID_LIST_LISTENER_HS =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4odbc)
      (ORACLE_HOME = /oracle/product/DG4ODBC)
      (SID_NAME = record)
      (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/product/DG4ODBC/lib:/oracle/product/10.2.0/lib)
    )
  )
I'm updating my /oracle/product/10.2.0/network/admin/tnsnames.ora :
RECORD2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cdxlan036)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SID = record)
    )
    (HS=OK)
   )
In last i'm creating the dblink :
CREATE  DATABASE LINK "RECORD"  CONNECT TO "rec_lct" IDENTIFIED BY "rec_lct"  USING 'RECORD2';
When i'm trying to select my postgres database, that doesn't work. Here the /oracle/product/DG4ODBC/hs/log/record.log :
[oracle@cdxlan036 log]$ cat record_agt_30445.trc


Oracle Corporation --- MONDAY    FEB 27 2012 17:11:31.204


Heterogeneous Agent Release
11.2.0.1.0




Oracle Corporation --- MONDAY    FEB 27 2012 17:11:31.204

    Version 11.2.0.1.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "4"
Entered hgosdip
 setting HS_OPEN_CURSORS to default of 50
 setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
 setting HS_FDS_RECOVERY_PWD to default value
 setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
 setting HS_IDLE_TIMEOUT to default of 0
 setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
 setting HS_NLS_NCHAR to default of "AL32UTF8"
 setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
 setting HS_FDS_DATE_MAPPING to default of "DATE"
 setting HS_RPC_FETCH_REBLOCKING to default of "ON"
 setting HS_FDS_FETCH_ROWS to default of "100"
 setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
 setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
 setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
 setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
 setting HS_FDS_MAP_NCHAR to default of "TRUE"
 setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
 setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
 setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
 setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
 setting HS_FDS_QUERY_DRIVER to default of "TRUE"
 setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
 Parameter HS_FDS_QUOTE_IDENTIFIER is not set
 setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
 setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
 setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
 setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
 setting HS_FDS_DELAYED_OPEN to default of "TRUE"
 setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
 ORACLE_SID is "record"
 Product-Info:
  Port Rls/Upd:1/0 PrdStat:0
  Agent:Oracle Database Gateway for ODBC
  Facility:hsa
  Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:record
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=31
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=31
HOCXU_SEM_VER=102000
Entered hgolofn at 2012/02/27-17:11:31
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/psqlodbc.so"
Entered hgolofns at 2012/02/27-17:11:31
 libname=/usr/lib64/psqlodbc.so, funcname=SQLAllocHandle
 peflerr=6521, libname=/usr/lib64/psqlodbc.so, funcname=SQLAllocHandle
 hoaerr:28500
Exiting hgolofns at 2012/02/27-17:11:31
Failed to load ODBC library symbol: /usr/lib64/psqlodbc.so(SQLAllocHandle)
Exiting hgolofn, rc=28500 at 2012/02/27-17:11:31
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs
Entered hgoexit
HS Gateway:  NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 FUNCTION:hgoexit() ID:Connection context
What's wrong with my configuration?

thanks for your help

regards,

Mathieu
  • 1. Re: Create a dblink oracle to postgres with dg4odbc
    mkirtley-Oracle Expert
    Currently Being Moderated
    Mathieu,
    The error causing the problem is this -

    Failed to load ODBC library symbol: /usr/lib64/psqlodbc.so(SQLAllocHandle)

    Which implies the psqlodbc.so file does not contain sqlallochandle symbol.
    However, your gateway listener has the following env setting -

    (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/product/DG4ODBC/lib:/oracle/product/10.2.0/lib)


    so can you remove the entry -

    /oracle/product/10.2.0/lib

    and stop and start the listener.
    Can you then check the psqlodbc.so file -

    cd usr/lib64
    nm psqlodbc.so | grep SQLAllocHandle

    or

    nm -D psqlodbc.so | grep SQLAllocHandle

    and check it exists in the file.

    Regards,
    Mike
  • 2. Re: Create a dblink oracle to postgres with dg4odbc
    919587 Newbie
    Currently Being Moderated
    Hi Mike

    thanks a lot for your reply

    see below the reply of command nm :
    oracle@cdxlan036 log]$ cd /usr/lib64/
    [oracle@cdxlan036 lib64]$ nm psqlodbc.so | grep SQLAllocHandle
    nm: psqlodbc.so: aucun symbole
    [oracle@cdxlan036 lib64]$
    [oracle@cdxlan036 lib64]$ nm -D psqlodbc.so | grep SQLAllocHandle
    [oracle@cdxlan036 lib64]$
    should i guess my driver odbc doesn't work correctly?

    regards
    Mathieu
  • 3. Re: Create a dblink oracle to postgres with dg4odbc
    kgronau Guru
    Currently Being Moderated
    HS_FDS_SHAREABLE_NAME needs to point to the ODBC driver manager. Commonly the ODBC driver manager is a separate library (common name is libodbc.so). It can be downloaded for example from unixodbc.org. Only a few rare ODBC drivers (some ODBC drivers from MySQL and also some IBM DB2 drivers have a built in ODBC driver manager into the ODBC driver itself, all other require a stand alone ODBC driver manager).

    Background info:
    In general the ODBC set up consists of 2 parts, an ODBC driver manager and an ODBC driver itself. The ODBC driver manager is providing generic ODBC functions and it is also responsible to load the driver specified in the odbc.ini file. The ODBC driver itself is then dedicated for a foreign database and provides specific foreign database functions.
  • 4. Re: Create a dblink oracle to postgres with dg4odbc
    919587 Newbie
    Currently Being Moderated
    Hi kgronau,

    thanks for your reply. Unfortunately, i'm not sure to understand what you explain.
    In fact i should have a driver manager and a driver, thats it?

    I've download and install this rpm : postgresql-odbc-7.3-8.RHEL4.1.x86_64.rpm.

    it provides :

    /usr/lib64/psqlodbc.la
    /usr/lib64/psqlodbc.so
    /usr/share/psqlodbc/odbc-drop.sql
    /usr/share/psqlodbc/odbc.sql

    I've supposed thats the /usr/lib64/psqlodbc.so file must be the drver manager. So i've pointed HS_FDS_SHAREABLE_NAME to this file.

    But where is the driver? Driver in my odbc.ini must point to the driver file, that's it?


    regards

    Mathieu
  • 5. Re: Create a dblink oracle to postgres with dg4odbc
    kgronau Guru
    Currently Being Moderated
    Yes, you need to have an ODBC driver (psqlodbc.so) and an ODBC driver manager (very common name is libodbc.so). So you're missing the ODBC driver manager which you can for example get from unixodbc.org and once you installed it you need to correct HS_FDS_SHAREABLE_NAME to refer to the driver manager. The driver manager will check out the odbc.ini file and depending on the specified DSN it will load your ODBC driver.
  • 6. Re: Create a dblink oracle to postgres with dg4odbc
    919587 Newbie
    Currently Being Moderated
    Hi

    thanks a lot for your help.

    i've another problem now with my dblink, but i prefer create a new thread.

    thanks

    Mathieu

Legend

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