Hi
I have to create dblink between two databases
1) Oracle 12c on Windows srv 64x
2)Postgres
I've created ODBC DSN using 64bit ODBC tool on windows machine - name:PG_LINK. Test works fine and i see "Connection succesfull
here are my config files:
----------------------initPG_LINK.ora
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 0
-----------------LISTENER.ORA
# listener.ora Network Configuration File: D:\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PG_LINK)
(ORACLE_HOME = d:\oracle\product\12.2.0\dbhome_1)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
----------------TNSNAMES.ORA
# tnsnames.ora Network Configuration File: D:\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
PG_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =(SID = PG_LINK))
(HS=OK)
)
OBIHD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = obihd)
)
)
LISTENER_OBI =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhst)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
I have dblink using this statement;
--------------------------------------------------------
-- DDL for DB Link PG_LINK
--------------------------------------------------------
CREATE DATABASE LINK "PG_LINK" CONNECT TO "dwh" IDENTIFIED BY VALUES ':1' USING 'PG_LINK';
but select from table using abocve dblink returns: ORA-28545
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 PG_LINK
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.
Error at Line: 2 Column: 19
ofcourse listener was reloaded
When do I miss anything? What is wrong?
Should I use 64 or 32 bit ODBC datasource (both connect to Postgres using windows tool)?
could you advise me?