My turn.
I know there are plenty of posts on this subject, but nothing I have found has worked. Would really appreciate some help with this one.
Goal: I'm trying to use ODBC to access a table in PostgreSQL 13 from Oracle. I have followed the guides here: https://docs.oracle.com/en/database/oracle/oracle-database/18/otgiw/config-odbc-gateway.html
O/S is Oracle Linux 8. Oracle XE 18.3 and PostgreSQL 13 are on the same server.
I always get the following error:
------------------------------------------------------------------------------------------------------
SQL> select name from test@pg;
select name from test@pg
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from PG
------------------------------------------------------------------------------------------------------
I have installed postgres and Linux ODBC:
------------------------------------------------------------------------------------------------------
❯ dnf list | grep -i postgresql-odbc
postgresql-odbc.x86_64 10.03.0000-2.el8 @ol8_appstream
postgresql-odbc.src 10.03.0000-2.el8 ol8_appstream
postgresql-odbc-tests.x86_64 10.03.0000-2.el8 ol8_appstream
❯ dnf list | grep -i unixodbc
unixODBC.x86_64 2.3.7-1.el8 @ol8_appstream
unixODBC-devel.x86_64 2.3.7-1.el8 @ol8_appstream
freeradius-unixODBC.x86_64 3.0.20-12.module+el8.6.0+20579+542eca26 ol8_appstream
unixODBC.i686 2.3.7-1.el8 ol8_appstream
unixODBC.src 2.3.7-1.el8 ol8_appstream
unixODBC-devel.i686 2.3.7-1.el8 ol8_appstream
------------------------------------------------------------------------------------------------------
... and configured the data source:
------------------------------------------------------------------------------------------------------
❯ cat /etc/odbc.ini
[ateapg]
Description=Atea Postgres ODBC
Driver=/usr/lib64/psqlodbc.so
ServerName=localhost
Username=atea
Password=XXXXXX
Port=5432
Database=atea
[Default]
Driver=/usr/lib64/liboplodbcS.so.2
------------------------------------------------------------------------------------------------------
I can use the ODBC client isql to query a table in the Postgres database:
------------------------------------------------------------------------------------------------------
❯ isql ateapg
<snip>
+---------------------------------------+
SQL> select age from test;
+------------+
| age |
+------------+
| 1024 |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
------------------------------------------------------------------------------------------------------
I have created the Oracle HS init*.ora:
------------------------------------------------------------------------------------------------------
❯ cat $ORACLE_HOME/hs/admin/initateapg.ora
HS_FDS_CONNECT_INFO = ateapg
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHARABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
set ODBCINI = /etc/odbc.ini
------------------------------------------------------------------------------------------------------
Have updated listener.ora as follows:
------------------------------------------------------------------------------------------------------
❯ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scm8)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
DEFAULT_SERVICE_LISTENER = XE
USE_SID_AS_SERVICE_LISTENER = on
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE)
(ENVS=LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/opt/oracle/product/18c/dbhomeXE/lib)
(SID_NAME=ateapg)
(PROGRAM=dg4odbc)
)
)
------------------------------------------------------------------------------------------------------
Have updated tnsnames.ora as follows:
------------------------------------------------------------------------------------------------------
❯ cat $ORACLE_HOME/network/admin/tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scm8)(PORT = 1521))
(CONNECT_DATA =
(SID = XE)
)
)
xepdb1 =
(DESCRIPTION =
(RETRY_COUNT=10)
(RETRY_DELAY=10)
(ADDRESS = (PROTOCOL = TCP)(HOST = scm8)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = xepdb1)
)
)
ateapg =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=scm8)(PORT=1521))
(CONNECT_DATA= (SID=ateapg))
(HS=OK)
)
------------------------------------------------------------------------------------------------------
After reloading the listener we can see the ateapg service.
------------------------------------------------------------------------------------------------------
❯ lsnrctl status
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 26-MAY-2022 07:28:15
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=scm8)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 25-MAY-2022 16:17:42
Uptime 0 days 15 hr. 10 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/scm8/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=scm8)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "XE" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "a635d3deb36cacc6e055000000000001" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "ateapg" has 1 instance(s).
Instance "ateapg", status UNKNOWN, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
------------------------------------------------------------------------------------------------------
The database link is then created as follows:
------------------------------------------------------------------------------------------------------
create public database link pg connect to "atea" identified by "XXXXX" using 'ateapg';
------------------------------------------------------------------------------------------------------