[unixODBC-support] Errors with integration between Oracle and PostgreSQL

Nick Gorham nick at lurcher.org
Tue Apr 7 16:01:54 BST 2009


Raul Orduna wrote:

>I was working with Oracle support because we can't use the database link from Oracle (DG4ODBC) to PostgreSQL through OWB (Oracle Warehouse Builder).
>
> 
>
>I test the Database link from SQLplus and runs properly. I only detect problems with text data of the system tables of postgreSQL (schema pg_catalog). Other tables and numeric values seem correct.
>
> 
>
>When we use Oracle Warehouse Builder this tool makes some "standard" queries: "dual" table, "all_users" table, etc, in order to show the tables for read data. These queries don't run because the tool report that tables don't exist. 
>
> 
>
>The response of Oracle is included below: 
>
>After researching this with several engineers on the gateways team, it was determined that your ODBC driver is returning an incorrect sqlstate of "HY000" in your dg4odbc trace (allusers_dg4odbc[1].log). The correct sqlstate for an "object not found" error is "42S02". This is what's architected in the ODBC spec, drivers are supposed to follow the spec and those that don't are broken. You will need to persue this with your ODBC driver vendor.
>
> 
>
>The referenced files are attached (logs of the same test for all_users in both logfiles, and two connections, the correct one from sqlplus, and the error one from OWB).
>
> 
>
>Any idea??
>
> 
>
>Raúl Orduna
>
>  
>
Its nothing but a hack, but the modified files in

ftp://ftp.unixodbc.org/pub/unixODBC/pgmod_for_dg40dbc.tar

untarred into psqlodbc-08.03.0400 source tree and rebuilt then installed 
might fix your problem.

Before:

> SQL> select * from demo at pg;
> select * from demo at pg
>               *
> ERROR at line 1:
> ORA-28500: connection from ORACLE to a non-Oracle system returned this 
> message:
> ERROR:  relation "DEMO" does not exist;
> No query has been executed with that handle
> ORA-02063: preceding 3 lines from PG


After:

> SQL> select * from demo at pg;
> select * from demo at pg
>               *
> ERROR at line 1:
> ORA-00942: table or view does not exist
> ERROR:  relation "DEMO" does not exist;
> No query has been executed with that handle
> ORA-02063: preceding 3 lines from PG


I must remember the phrase "This is what's architected in the ODBC spec, 
drivers are supposed to follow the spec and those that don't are broken. 
You will need to persue this with your ODBC driver vendor".

Then again dg4odbc didn't work on Windows with the MS SQL Server driver 
until someone (Easysoft) pointed out to Oracle that UNICODE is meant to 
be 16 bits, despite what other vendors told them :-)

-- 
Nick


More information about the unixODBC-support mailing list