[ psqlodbc-Bugs-1000639 ] Connect PostgreSQL to Oracle 10g through ODBC: can"t read all_tables

Bugs item #1000639, was opened at 2006-05-09 15:48
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 39&group_id=1000125

Category: None
Group: None
>Status: Closed
Resolution: None
Priority: 3
Submitted By: Nobody (None)
Assigned to: Nobody (None)
Summary: Connect PostgreSQL to Oracle 10g through ODBC: can't read all_tables

Initial Comment:
Oracle 10g is not able to read the database table names (all_tables and all_objects, es. select * from all_tables@hspostgresodbc) from PostgreSQL using PostgreSQL ODBC (v. 08.01.0200). This functionality is essential to be able to use Oracle Warehouse Builder and import external database definitions (heterogeneous services).
However, Oracle (SQL*Plus) is able to retrieve records when the table name is stated in the SQL query (es. select * from “my_table”).
The problem seems to be with the PostgreSQL ODBC because Oracle is able to read schemes from other databases through ODBC. I’ve done some tests with Access dbs just to be sure that the problem is not with my system (Windows XP SP2).
The problem appears both with ANSI and UNICODE PostgreSQL drivers.
Best regards,
Raoni


------------------------------------------------------------ ----------

>Comment By: Ludek Finstrle (luf)
Date: 2006-06-27 12:16

Message:
No user response.

------------------------------------------------------------ ----------

Comment By: Ludek Finstrle (luf)
Date: 2006-05-28 14:30

Message:
The user sends me the mylog output from 08.01 but not from 08.02. I ask him for mylog output from 08.02 and he answer that there is new oracle part and he want to test it before he sends me the mylog output ...

------------------------------------------------------------ ----------

Comment By: Nobody (None)
Date: 2006-05-15 16:26

Message:
Dear Luf and psqlODBC developers,

Looking closer to the debug and comm logs from the driver 8.01.0200, I’ve found out that the problem was generated after a table wasn’t found (the driver seems to search for the all_tables table, and just afterwards to query the scheme pg_catalog). In this case the all the following queries returned with the error “ERROR: current transaction is aborted, commands ignored until end of transaction block”. Maybe this problem is related with the bug [#1000641] http://pgfoundry.org/tracker/index.php?func=detail&aid=10006 41&group_id=1000125&atid=538

Following Luf’s suggestion, I’ve installed the psqlODBC v 08.02.0002 and I’ve got a completely different result.
As before, the simple query “select * from my_table@hsodbc” returns the result using SQL*Plus clearly faster than before.
Doing the query “select * from all_tables@hsodbc” the result is OK (the name of 56 tables) but the process is very slow and burdensome. The file paging memory in the machine running Oracle and the SQL*Plus jumps from the normal 700 MB to 1.7 GB, and the query takes about 20 minutes to complete.
Also the 1.7 GB remains allocated and the query process halted in PostGreSQL even after the on screen result of the query. The memory is freed and the process terminated after the logging out SQL*Plus.
Probably as result of this memory explosion, the system runs out of memory and Oracle Warehouse Builder selecting data base link -> schema, after some minutes get an error similar to: “can’t read from memory address XXXXX”
All those test where done with PostGreSQL 8.0.1 (on Linux Suse 9.0) and Oracle 10g (Windows XP SP2).

To be sure that the problem was not related to the PostGreSQL version, we updated the server to the 8.1.3 (running in Linux Suse 9.0 with psqlODBC v 08.02.0002 ) and OWB couldn’t see any table as before and doing Select * from all_tables@hsodbc we’ve got the message:

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]
ORA-02063: preceding 2 lines from HSPG

no rows selected

On the other hand, with the same configuration as before (Oracle 10g under Windows XP SP1, psqlODBC v 08.02.0002) but with PostGreSQL 8.1.3 under Windows XP SP1, OWB can read table names (same tables imported from Linux PostgreSQL) but not select " from all_tables (a minor problem, because what is important for us is to make OWB work with PostgreSQL).

The encoding for all DBs is UTF8.

Any ideas of where could be the problem?


------------------------------------------------------------ ----------

Comment By: Ludek Finstrle (luf)
Date: 2006-05-09 16:18

Message:
Please, could you try psqlodbc 08.02.0002?

Regards,

Luf

------------------------------------------------------------ ----------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 39&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match