Oracle Generic DB Link not working

Question

I'm trying to use oracle's hsodbc generic database link driver to access a postgresql database from my oracle 10gr2 database server. I think I have everything configured but I'm receiving this error from the sqlplus promt after trying a remote query.

SQL> select * from temp_user@intranet;
select * from temp_user@intranet
                        *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from INTRANET

If I use "isql" from the linux command line (in other words test just the odbc connection) the query works.

I enter in "isql intranet" (intranet is the name of the odbc connection) I get the prompt I type select * from temp_user and I receive back my 157 records on screen.

So I know the odbc configuration is setup correctly. Here is what I do for oracle.

%oracle_home/hs/admin/inithsodbc.ora
HS_FDS_CONNECT_INFO = intranet
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/bin/ODBCConfig

%oracle_home/network/admin/tnsnames.ora
INTRANET =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.1)(PORT = 5432))
    )
    (CONNECT_DATA =
      (SID = INTRANET)
    )
    (HS = OK)

%oracle_home/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = INTRANET)
      (PROGRAM = hsodbc)
      (SID_NAME = INTRANET)
      (ORACLE_HOME = /home/oracle/app/OraHomeTEST)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb.andersen-const.com)(PORT = 5432))
    )
  )

I have restarted the listener. It's status is as follows.

Services Summary...
Service "INTRANET" has 1 instance(s).
  Instance "INTRANET", status UNKNOWN, has 1 handler(s) for this service...

I then go into sqlplus from the database server command line and do the following.

drop database link intranet;

create database link intranet connect to auser identified by apassword using 'intranet';

This is successful.

However when I run

 select * from temp_user@intranet

I receive the error

ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from INTRANET

I've spend atleast a good day going back over the configures and trying things and I always get this error.

Anybody have any good ideas,

DCookie DCookie · Accepted Answer · 2009-06-25T16:14:27

What does "tnsping intranet" report?

Are you sure your hsodbc prorgram is in the Oracle_home/bin directory of the your gateway installation? Also, is your LD_LIBRARY_PATH set properly?

I believe your LD_LIBRARY_PATH should be $ORACLE_HOME/lib. Sorry, not sure since I don't do much with *Nix these days.

Oracle Generic DB Link not working

1 Answers