0

So I'm trying to create a database link between oracle and postgresql in the same server, and when I try to do a select it shows me this error. I've been looking for some answers in different forums but my low knowledge about it doesn't let me understand a way to solve the problem. This is the error:

ERROR line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Cannot retrieve text from RED / NCR 65535 message
ORA-02063: 2 lines preceding POSTGRESQLLINK

Here is my tnsnames.ora:

test=(description=
(address_list=
        (address=(protocol=TCP)(host=localhost)(port=5432)))
                (connect_data=(service_name=test)))

POSTGRESQL =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SID = postgresql)
)
(HS = OK)
  )

My listener.ora:

    DEFAULT_SERVICE_LISTENER = XE

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )

    SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC=
         (SID_NAME=postgresql)
         (ORACLE_HOME=$ORACLE_HOME)
         (PROGRAM=dg4odbc)
       )
     )

My odbc.ini:

[sallydb]
Description=PostgreSQL connection to SallyDB
Driver=PostgreSQL
Database=sallydb
Servername=192.168.1.15
UserName=sally
Password=sallypassword
Port=5432
Protocol=9.3
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ConnSettings=

[test]
Description=PostgreSQL
Driver=PostgreSQL ANSI
Trace=No
TraceFile=/tmp/psqlodbc.log
Database=bahia
Servername=localhost
UserName=postgres
Password=patata
Port=5432
ReadOnly=Yes
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No
ConnSettings=

[PostgreSQL test]
Description=PostgreSQL
Driver=PostgreSQL ANSI
Trace=No
TraceFile=/tmp/psqlodbc.log
Database=template1
Servername=localhost
UserName=
Password=
Port=
ReadOnly=Yes
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No
ConnSettings=

And my odbcinst.ini:

    [PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=3

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=3

Sorry if this is an easy problem.

Jolvary Jimenez Ortiz
  • 1
  • 1
  • Are you accessing Oracle from PostgreSQL or vice versa? Which software are you using for this? Please add an appropriate tag. – Laurenz Albe Dec 10 '19 at 03:31
  • And how exactly do you create the "link" from Oracle to Postgres? If that is an Oracle `create database link` it won't work, that only works from Oracle to Oracle. –  Dec 10 '19 at 06:47
  • I tried with create database link, cause looking around was the only way I saw. How can you connect from oracle to postgresql then? @a_horse_with_no_name – Jolvary Jimenez Ortiz Dec 10 '19 at 11:29
  • I was accesing postgre from oracle, in an ubuntu server 16.04. But if you can tell me a way to connect to oracle from postgresql it could be better, thanks. @LaurenzAlbe – Jolvary Jimenez Ortiz Dec 10 '19 at 11:31
  • 1
    To access Postgres from Oracle you need [heterogeneous services](https://oracle-base.com/articles/misc/heterogeneous-services-generic-connectivity). To access Oracle from Postgres you only need [oracle_fdw](https://github.com/laurenz/oracle_fdw) –  Dec 10 '19 at 11:37

0 Answers0