Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to setup a dblink from Oracle to Postgresql?

663318Feb 22 2012 — edited Feb 22 2012
I try this query from Oracle :
    select * from "a_table"@Postgres;
Here is the error message I get :
    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: précédant 2 lines de RECORD
    28545. 0000 -  "error diagnosed by Net8 when connecting to an agent"
    *Cause:    An attempt to call an external procedure or to issue SQL
           to a non-Oracle system on a Heterogeneous Services database link
           failed at connection initialization.  The error diagnosed
           by Net8 NCR software is reported separately.
    *Action:   Refer to the Net8 NCRO error message.  If this isn't clear,
           check connection administrative setup in tnsnames.ora
           and listener.ora for the service associated with the
           Heterogeneous Services database link being used, or with
           'extproc_connection_data' for an external procedure call.
    Erreur à la ligne 1, colonne 25
Here is tnsnames.ora
    Postgres =
       (DESCRIPTION =
         (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SID = RECORD)
       )
       (HS=OK)
      )
Here is listener.ora
	LISTENER_HS =
	  (DESCRIPTION_LIST =
		(DESCRIPTION =
		  (ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1525))
			(ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
		  )
		)
	  )

	SID_LIST_LISTENER_HS =
	  (SID_LIST =
		(SID_DESC =
		  (SID_NAME = RECORD)
		  (ORACLE_HOME = /oracle/product/10.2.0)
		  (PROGRAM = hsodbc)
		  (ENVS = "LD_LIBRARY_PATH=/oracle/product/10.2.0/lib32:/oracle/product/10.2.0/hs/lib32:/user/lib")
		)
		(SID_DESC =
		  (SID_NAME = PLSExtProc)
		  (ORACLE_HOME = /oracle/product/10.2.0)
		  (PROGRAM = extproc)
		)
	  )
Note after starting the listener, here is the output
	$ lsnrctl status LISTENER_HS

	LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 22-FEB-2012 13:45:09

	Copyright (c) 1991, 2006, Oracle.  All rights reserved.

	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1525)))
	STATUS of the LISTENER
	------------------------
	Alias                     LISTENER_HS
	Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
	Start Date                22-FEB-2012 13:45:05
	Uptime                    0 days 0 hr. 0 min. 3 sec
	Trace Level               off
	Security                  ON: Local OS Authentication
	SNMP                      ON
	Listener Parameter File   /oracle/product/10.2.0/network/admin/listener.ora
	Listener Log File         /oracle/product/10.2.0/network/log/listener_hs.log
	Listening Endpoints Summary...
	  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1525)))
	  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
	Services Summary...
	Service "PLSExtProc" has 1 instance(s).
	  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
	Service "RECORD" has 1 instance(s).
	  Instance "RECORD", status UNKNOWN, has 1 handler(s) for this service...
	The command completed successfully
Here initPostgres.ora
	HS_FDS_CONNECT_INFO = Postgres
	HS_FDS_TRACE_LEVEL = DEBUG
	HS_FDS_TRACE_FILE_NAME = /oracle/product/10.2.0/hs/log/postgres.trc
	HS_FDS_SHAREABLE_NAME = /usr/lib/psqlodbc.so

	set ODBCINI= /etc/odbc.ini
`/oracle/product/10.2.0/hs/log/record.trc` is empty.


What am I missing ?

Oracle 10gR2<br>
Linux RedHat 64bit<br>
<br>
Postgresql 8.3<br>
Linux RedHat 64bit<br>
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 21 2012
Added on Feb 22 2012
3 comments
2,573 views