Nice day !
I want to create connection from oracle to postgres.
Here are my configs:
HS_FDS_CONNECT_INFO = PSQL
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so
5/ Driver POSTGRESQL ODBC:
Description=ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
But when I create database link :
CREATE public DATABASE LINK l test
CONNECT TO "postgres" IDENTIFIED BY "123456"
SELECT id FROM tb_abc@luanmap_test ;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from LUANMAP_TEST
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
Error at Line: 9 Column: 33
Please help me or give me some advices. Thanks.
Look at HS_FDS_SHAREABLE_NAME - you have configured it pointing to the ODBC Driver "HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so" but all PostGres ODBC Drivers I know don't have all ODBC functions built in - hence it requires also an ODBC Driver Manager like for example the Driver Manager from www.unixODBC.org.
So please check out if you already have an ODBC Driver Manager installed on your machine and if the word size of the Driver Manager matches the owrd size of the ODBC Driver as well as DG4ODBC. If you don't have an ODBC Driver manager, please get it from UnixODBC and apply it to your system. Once done, change the configuration and make sure HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager instead of the ODBC driver itself.
Root cause for the missing handle (SQLAllocHandle) is that the Postgres ODBC driver does not include generic ODBC functions and also requires a Driver Manager. So yes, please go ahead and apply the UnixODBC Driver Manager to your env.
Installing unixODBC this way is commonly fine but please make sure it is a 2.3.x release.
Regarding the LD_LIBRARY_PATH in the listener.ora file I commonly prefer to set it there as well, but in general just having all libs specified in the LD_LIBRARY_PATH for the user who starts the Oracle listener is also fine.
When you specify it in the listener.ora file, please use:
Would be good to see also the error message from SQL*Plus ... so I can just guess and would suggest to add:
HS_NLS_NCHAR = UCS2
to the gateway config file. Then please make sure to close SQL+plus and connect again, then test the DB link.
So as the basic test you should test with isql, an ODBC test utility shipped with unixODBC. This isql tool requires the environment variable ODBCINI which must be set to the odbc.ini file. According to he gateway init file you're using this odbc.ini file: /etc/odbc.ini
Does it contain a DSN pointing to your PostgreSQL database?
The DSN will also include DRIVER parameter pointing to the ODBC Driver itself (DRIVER=/usr/lib/libodbcpsql.so) as well as Database, Servername and Port. There are a couple of additional parameters which you might have to set - so best would be to check the PostgreSQl ODBC docu for the driver version you're using.
Yes, LD_LIBRARY_PATH setting is fine.
When using a named Driver configuration Driver = PostgreSQL you also need to refer to the odbcinst.ini file - I commonly prefer to specify the driver and the full path in the odbc.ini file like:
So once you exported ODBCINI environment variable:
export ODBCINI=/etc/odbc.ini test the connection with isql (isql DNS_PSQL uid pwd)