Oracle Database Discussions

Hello everyone,

I've done some searching around to try and find a similar post, but I could not find one that was the same. So forgive me if there is already a similar posting...

We're trying to do a dblink so that Oracle can pull information from Denodo. So we're linking to PostgreSQL. I've loaded the unixODBC drivers, PostgreSQL ODBC drivers into the ODBC Driver Manager. When I use ISQL to test the DSN it comes up ok... Any help would be appreciated. The end of the trc file that I posted it throws the error "Failed to load ODBC library symbol: /usr/lib64/psqlodbcw.so(SQLGetDescRecW)" Which the only thing I could find on that in a different forum was something about the TimesTen function not being in the unixODBC but we're not using TimesTen.

First, here is the system I am using and versions of certain software I've installed.

Linux version 2.6.32.12-0.7-default (geeko@buildhost) (gcc version 4.3.4 [gcc-4_3-branch revision 152973] (SUSE Linux) ) #1 SMP 2010-05-20 11:14:20 +0200

I have installed the latest unixODBC drivers/driver manager that version is: unixODBC 2.3.1

I'm also using the latest PostgreSQL ODBC Drivers. That is version: psqlodbc- 09.01.0100

We are using Oracle 11.2.0

PostgreSQL 8.3.9

Here is my odbc.ini file:

-----

[VDP_acme_DSN]
Description = VDP connection
Driver = postgreSQLDriver
Trace = No
TraceFile = /home/oracle/odbc/trace.log
Debug = No
DebugFile = /home/oracle/odbc/debug.log
Database = admin
Servername = denodoserver
UserName = admin
Password = *********
Port = 9996
Protocol = 7.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
LFConversion = Yes
UpdatableCursors = Yes
BoolsAsChar = Yes
UniqueIndex = Yes
ServerType = Postgres
ConnSettings =
UseServerSidePrepare= 0
ByteaAsLongVarBinary= 1
Optimizer = 0
Ksqo = 0
----

The odbcinst.ini
----

[postgreSQLDriver]
Description = PostgreSQL 09.01.0100 driver
Driver = /usr/local/lib/psqlodbcw.so (it is 64-bit... it's installed into lib but I ran file on it and it's 64 bit)
UsageCount = 1

----

Here's the template that I used to install the driver into the drivermanager via odbcinst

----

[postgreSQLDriver]
Descrtiption=PostgreSQL 09.01.0100 driver
Driver=/usr/local/lib/psqlodbcw.so
UsageCount=1

----

Here's the output showing the driver in the driver manager

----

server-app:/app/oracle/product/11.2.0/tghome_1/hs/log # odbcinst -q -d
[postgreSQLDriver]

----

Template creating the DSN

----

[VDP_acme_DSN]
Description = VDP connection
Driver = postgreSQLDriver
Trace = No
TraceFile = /home/oracle/odbc/trace.log
Debug = No
DebugFile = /home/oracle/odbc/debug.log
Database = admin
Servername =denodoserver
UserName = admin
Password = *********
Port = 9996
Protocol = 7.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
LFConversion = Yes
UpdatableCursors = Yes
BoolsAsChar = Yes
UniqueIndex = Yes
ServerType = Postgres
ConnSettings =
UseServerSidePrepare= 0
ByteaAsLongVarBinary= 1
Optimizer = 0
Ksqo = 0
----

listener.ora
----

LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))
(ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
)
)

SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(SID_NAME = VDP_acme_DSN)
(ORACLE_HOME = /app/oracle/product/11.2.0/tghome_1)
(ENV="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/app/oracle/product/11.2.0/tghome/lib")
(PROGRAM = dg4odbc)
)
)

#CONNECT_TIMEOUT_LISTENER = 0
----
tnsnames.ora

----

LISTENER_DG =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1527))
(CONNECT_DATA=(SERVICE_NAME=LISTENER_DG))
)
VDP_acme_DSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1527))
(CONNECT_DATA=(SERVICE_NAME=VDP_acme_DSN))
)
ORAETL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORAETL)
)
)
----

I'm not sure what other files are needed... So here are the errors I've been getting. First when we use Oracle SQL Developer and do a test connection we get the following

----
an error was encountered performing the requested operation:

ORA-28500: Connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line from DENODO_DBLINK
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
* Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded message.
Vendor code 28500
----

Oracle trace throws this error

----

server-app:/app/oracle/product/11.2.0/tghome_1/hs/log # cat VDP_acme_DSN_agt_12224.trc


Oracle Corporation --- TUESDAY JUN 12 2012 15:19:44.099


Heterogeneous Agent Release
11.2.0.1.0




Oracle Corporation --- TUESDAY JUN 12 2012 15:19:44.099

Version 11.2.0.1.0

Entered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
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 "VDP_acme_DSN"
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:VDP_acme_DSN
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=873
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=873
HOCXU_SEM_VER=112000
Entered hgolofn at 2012/06/12-15:19:44
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/psqlodbcw.so"
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929e750
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929c5f0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe9299440
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929c5c0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929a7d0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929bed0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929e480
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929bcd0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929bbd0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929e230
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929bb90
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929ba30
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929da70
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929b990
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929a230
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929b760
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929b6e0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929b5a0
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929b520
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929d950
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe929d850
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a3200
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a5b40
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a5a00
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a5440
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a5780
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a5320
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a4550
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a3a00
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
symbol_peflctx=0xe92a3650
hoaerr:0
Exiting hgolofns at 2012/06/12-15:19:44
Entered hgolofns at 2012/06/12-15:19:44
hoaerr:28500
Exiting hgolofns at 2012/06/12-15:19:44
Failed to load ODBC library symbol: /usr/lib64/psqlodbcw.so(SQLGetDescRecW)
Exiting hgolofn, rc=28500 at 2012/06/12-15:19:44
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
----

I hope that's everything... Thanks