Good morning everyone, this week I have been intensively trying to create a connection between a motor and a postgresql oracle engine, but have not succeeded was following the recommendations Kgronau-Oracle user through the thread https://community.oracle.com/thread/3560250?start=0&tstart=0, but has not worked and error that comes out when I run the following query in sqlplus is:
select * from "user"PG_LINK;
ERROR at line 1:
ORA-28500: Oracle connection to a non-Oracle system returned this
Post:
ORA-02063: preceding line PG_LINK
I made settings as follows:
Oracle Linux 6.5 64-bits
i created a file in /home/oracle/.odbc.ini
with the following information:
[PG_LINK]
Driver = /usr/pgsql-9.1/lib/psqlodbcw.so
Servername = 172.16.2.14
Username = user
Password = pass
Port = 5432
Database = database
Trace = yes
Tracefile = /tmp/database.log
With the permissions: -rw-r - r-- oracle oinstall
The driver install through: yum install postgresql91-odbc.x86_64
version 09.03.0400-1PGDG.rhel6
Also installed the unixODBC-2.3.0 package compiling the source and the result of odbcinst -j command the result is:
unixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
$ ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
I also created a file $ORACLE_HOME/hs/admin/initPG_LINK.ora with the following information:
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.1/lib/psqlodbcw.so
HS_FDS_SQLLEN_INTERPRETATION = 32
set ODBCINI = /home/oracle/.odbc.ini
Files $ORACLE_HOME/network/admin/listener.ora and tnsnames.ora were also modified as well:
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PG_LINK)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(ENV = "LD_LIBRARY_PATH = /usr/lib64:/usr/pgsql-9.1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/bin")
(PROGRAM = dg4odbc)
)
(SID_DESC =
(GLOBAL_DBNAME = SIAPROD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = SIAPROD)
)
)
tnsnames.ora
SIAPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.1.55) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SIAPROD)
)
)
PG_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.1.55) (PORT = 1521))
(CONNECT_DATA = (SID = PG_LINK))
(HS = OK)
)
Finally I followed these recommendations:
So let's create a file strace to see if we get more details from esta trace.
1. Get the process id of the Oracle listener That spawns the gateway (ps -ef | grep TNS)
2. strace -o -fae dg4odbc.log -p <gateway listener process id>
3. Now start a new SQL * Plus session and try to select from the Postgres database again. Once you get the mistake, cancel the strace process from step 2 dg4odbc.log and upload the file to a public file share.
- Klaus
http://www.mediafire.com/download/3kglaeg023v3r9r/dg4odbc.log
I hope you can help me solve this problem
Thanks in advance.