Forum Stats

  • 3,768,548 Users
  • 2,252,809 Discussions
  • 7,874,617 Comments

Discussions

Heterogenous connection to PostgreSQL doesn't work, no error output from driver

I've tried to configure heterogeneous connection to PostgreSQL, everything seems to work fine but after any remote query I get the following message error:

SQL> select * from "testtable"@pg_link;

select * from "testtable"@pg_link

                         *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line from PG_LINK


My configuration files:

/etc/odbc.ini

[PostgreSQL]                                                                                

Description = PostgreSQL                                                                    

Driver = /usr/lib64/psqlodbcw.so                                                            

ServerName = 192.168.1.14                                                                   

Username = user                                                                        

Password = password                                                                         

Port = 5432                                                                                 

Database = db                                                            

Trace = yes                                                                                 

TraceFile = /tmp/odbctrace.txt


/etc/odbcinst.ini

[PostgreSQL]

Description    = ODBC for PostgreSQL

Driver         = /usr/lib64/psqlodbcw.so

Setup          = /usr/lib64/libodbcpsqlS.so

Driver64       = /usr/lib64/psqlodbcw.so

Setup64        = /usr/lib64/libodbcpsqlS.so

FileUsage      = 1


$ORACLE_HOME/network/admin/listener.ora:

SID_LIST_LISTENER =

                 (SID_LIST =

                                 (SID_DESC =

                                     (SID_NAME = PROJEKTRBD)

                                     (ORACLE_HOME = /u01/app/oracle/product/21.0.0/dbhome_1\

)

                           )

                                 (SID_DESC =

                                     (SID_NAME = CBD1)

                                     (ORACLE_HOME = /u01/app/oracle/product/21.0.0/dbhome_1\

)

                           )


                           (SID_DESC =

                                     (SID_NAME = PostgreSQL)

                                     (ORACLE_HOME = /u01/app/oracle/product/21.0.0/dbhome_1\

)

                                       (ENVS="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/pr\

oduct/21.0.0/dbhome_1")

                                     (PROGRAM = dg4odbc)

                           )

                 )


$ORACLE_HOME/network/admin/tnsnames.ora

PostgreSQL =

                       (DESCRIPTION =

                                    (ADDRESS =

                                             (PROTOCOL = TCP)

                                             (HOST = localhost)

                                             (PORT = 1521)

                                    )

                                    (CONNECT_DATA =

                                             (SID = PostgreSQL)

                                    )

                                    (HS = OK)

                       )


$ORACLE_HOME/hs/admin/initPostgreSQL

# HS init parameters

HS_FDS_CONNECT_INFO = PostgreSQL

HS_FDS_TRACE_LEVEL = ON

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9


# ODBC specific environment variables

set ODBCINI=/etc/odbc.ini


When running "isql -v PostgreSQL", it works ok and I can query the remote database, so I guess the ODBC Data source is configured fine. Running "tnsping PostgreSQL" returns :

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) (CONNECT_DATA = (SID = PostgreSQL)) (HS = OK))

OK (10 msec)

So seemingly no problem here either. However whenever running any query from sqlplus it shows the mentioned error. No logs show up in /hs/log or /tmp, it seems like dg4odbc isn't working at all.