Oracle Heterogeneous Services for PostgreSQL (ODBC Driver): Error (HY000,NativeErr = 1)

Hi all!
Some time ago a client reported issues when integrating Oracle and PostgreSQL. The error:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
No query has been executed with that handle;
Could not send Query(connection dead) {HY000,NativeErr = 1}

Braking down errors:
Oracle: Connection from ORACLE to a non-Oracle system returned this message
PostgreSQL: {HY000,NativeErr = 1} Could not send Query(connection dead)

Both are general errors:
– Oracle’s one is for any error returned by remote service, when using Oracle Heterogeneous Services (old Database Gateway).
– Postgre’s one is for connections ended.

I also found some other similar errors:
Oracle: ORA-28511: lost RPC connection to heterogeneous remote agent using SID
PostgreSQL: {08S01,NativeErr = 26} Error fetching next row

In summary, the root cause was a firewall configuration ending connection. But what I want to share with you the workaround. 🙂
You know that sometimes, due certain rules, firewall rules may require formal change requests and procedures, so what you can do on database side is basically disable parameter UseDeclareFetch (default is false) in ODBC descriptor, as per below.

#Original:

[oracle@server01 admin]$ cat /etc/odbc.ini
[mydbpost]
Driver = PostgreSQL
Database = mydbpost
Servername = 192.168.120.120
Port = 5432
ReadOnly = Yes
Username = query_oracle
Password = ********
UseDeclareFetch = 1
[oracle@server01 admin]$

# After:

[oracle@server01 admin]$ cat /etc/odbc.ini
[mydbpost]
Driver = PostgreSQL
Database = mydbpost
Servername = 192.168.120.120
Port = 5432
ReadOnly = Yes
Username = query_oracle
Password = ********
[oracle@server01 admin]$

From PostgreSQL.Org:
Use Declare/Fetch: If true, the driver automatically uses declare cursor/fetch to handle SELECT statements and keeps 100 rows in a cache.
This is mostly a great advantage, especially if you are only interested in reading and not updating. It results in the driver not sucking down lots of memory to buffer the entire result set. If set to false, cursors will not be used and the driver will retrieve the entire result set. For very large tables, this is very inefficient and may use up all the Windows memory/resources. However, it may handle updates better since the tables are not kept open, as they are when using cursors.

In summary, the DML transactions were taking to long, and with this performance improvement, the performance on DML has improved and timeouts stopped.

Hope it helps!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s