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,

thanks

tim

Answer 1

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.