Thread: Problems with ORACLE

Problems with ORACLE

From

Alejandro Fuentes González

Date:

29 July 2003, 00:10:10

Hi, when I use the psqlODBC with MS-Access I´ve got not problems but when I try to use it with Oracle the next message appears:

SELECT * FROM "ORACLE"."TABLITA"@POSGRES

ERROR at line 1:

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

message:

[Generic Connectivity Using ODBC]Record &SQLREC has no fields. Loading failed

ORA-02063: preceding 2 lines from POSGRES

Altougth I´ve got already fields in the tables, I´ve tried with differnt kind of datatypes varchar char int numeric....but the problem persist

Any Idea???

Thanks in advance

Re: Problems with ORACLE

From

"Schleis, David"

Date:

29 July 2003, 14:01:06

Using Oracle 8.1.6 I was never successful in accessing a PostgreSQL database. Examination of the log files revealed that the Oracle ODBC driver uses double quotes "" inconsistently creating and then calling a table alias. There was no way around this, so it was impossible to access the PostgreSQL database from Oracle. I posted these findings on the OTN Heterogeneous Services discussion group and was told I needed to contact Oracle support. I moved on to other projects and I have not tried again with later versions of the Oracle ODBC driver. If you find a way to get Oracle to talk to PostgreSQL please let me know.

-- dave

David Schleis
Wisconsin State Laboratory of Hygiene
and Occupational Health Laboratory

email:        dave@mail.slh.wisc.edu
snail:         Madison WI 53707-7996
ups/fedx:    2601 Agriculture Dr
                 Madison WI 53718
voice:         608.224.6227
fax:            608.224.6267

-----Original Message-----
From: Alejandro Fuentes González [mailto:alejandro.fuentes@SSP.DF.GOB.MX]
Sent: Monday, July 28, 2003 7:08 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Problems with ORACLE

Hi, when I use the psqlODBC with MS-Access I´ve got not problems but when I try to use it with Oracle the next message appears:
SELECT * FROM "ORACLE"."TABLITA"@POSGRES
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:
[Generic Connectivity Using ODBC]Record &SQLREC has no fields. Loading failed
ORA-02063: preceding 2 lines from POSGRES
Altougth I´ve got already fields in the tables, I´ve tried with differnt kind of datatypes varchar char int numeric....but the problem persist
Any Idea???
Thanks in advance

Re: Problems with ORACLE

From

Alejandro Fuentes González

Date:

01 August 2003, 19:35:28

David, I have talked with the people of Oracle and they told me that:

El problema radica en la diferencia del comportamiento default de los simbolos, Oracle transforma todo a mayusculas, postgres a minusculas.
En el momento de resolver la query, esta se transforma en
select field from table@link
-> select "A1"."FIELD" from "TABLE" A1
Como el ultimo alias posgres lo transforma a minusculas este es equivalente a
-> select "A1"."FIELD" from "TABLE" "a1"

El cual no puede resolver.
TRANSLATING:

THE PROBLEM IS THE DIFFERENT BEHAVIOR OF THE DEFAULT SIMBOLS, ORACLE TRANSFORMS EVERYTHING TO UPPERCASES, POSTGRES TO LOWECASES.

WHEN THEY TRY TO RESOLVE THE QUERY ITS´ TRANSLATE FROM:

SELECT FIELD FROM TABLE@LINK

TO:

TO SELECT "A1"."FIELD" FROM "TABLE" A1

BECAUSE THE LAST ALIAS POSTGRES TRANSLATES TO LOWERCASE THIS IS EQUIVALENT TO:

SELECT "A1"."FIELD" FROM "TABLE" "a1"

THE WICH ONE CANNOT BE RESOLVE.

THEY ARE ASKING ME IF THERE IS A WAY TO CHANGE THIS BEHAVIOR.

You have already told me that there is not a way around so they are going to declare this as a bug.

-----Mensaje original-----
De: Schleis, David [mailto:dave@mail.slh.wisc.edu]
Enviado el: Martes, 29 de Julio de 2003 09:01 a.m.
Para: 'pgsql-odbc@postgresql.org'
Asunto: Re: [ODBC] Problems with ORACLE

Using Oracle 8.1.6 I was never successful in accessing a PostgreSQL database. Examination of the log files revealed that the Oracle ODBC driver uses double quotes "" inconsistently creating and then calling a table alias. There was no way around this, so it was impossible to access the PostgreSQL database from Oracle. I posted these findings on the OTN Heterogeneous Services discussion group and was told I needed to contact Oracle support. I moved on to other projects and I have not tried again with later versions of the Oracle ODBC driver. If you find a way to get Oracle to talk to PostgreSQL please let me know.
-- dave
David Schleis
Wisconsin State Laboratory of Hygiene
and Occupational Health Laboratory
email:        dave@mail.slh.wisc.edu
snail:         Madison WI 53707-7996
ups/fedx:    2601 Agriculture Dr
                 Madison WI 53718
voice:         608.224.6227
fax:            608.224.6267
-----Original Message-----
From: Alejandro Fuentes González [mailto:alejandro.fuentes@SSP.DF.GOB.MX]
Sent: Monday, July 28, 2003 7:08 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Problems with ORACLE

Hi, when I use the psqlODBC with MS-Access I´ve got not problems but when I try to use it with Oracle the next message appears:
SELECT * FROM "ORACLE"."TABLITA"@POSGRES
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:
[Generic Connectivity Using ODBC]Record &SQLREC has no fields. Loading failed
ORA-02063: preceding 2 lines from POSGRES
Altougth I´ve got already fields in the tables, I´ve tried with differnt kind of datatypes varchar char int numeric....but the problem persist
Any Idea???
Thanks in advance