Oracle Database Discussions

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