Re: Oracle Heterogenous Services Error

Sorry to step into this so late, but when I saw that you were using oracle 10g I
did not think it relevant. Please see the attached set of emails exchanged a few
years back regarding this topic, and a TAR that Oracle says they have completed.
Since my initial effort I have not again attempted to use Oracle HS with
PostgreSQL.

-- dave

David Schleis
Wisconsin State Laboratory of Hygiene

email: dave(at)mail(dot)slh(dot)wisc(dot)edu
snail: Madison WI 53718-6777
ups/fedx: 2811 Agriculture Dr
Madison WI 53718
voice: 608.221.6288
fax: 608.221.6297

+++++++++++++++++++++++++++++++++++++++++++++++++++
Yes I do the TAR númber is: 13953728.6

We are in touch (I hope they release the patch soon, while, the systems area in
my company are going to develope a kind of non real-time "replication" program.

Greetings

Alejandro.

-----Mensaje original-----
De: Schleis, David [mailto:dave(at)mail(dot)slh(dot)wisc(dot)edu]
Enviado el: Lunes, 04 de Agosto de 2003 06:51 a.m.
Para: 'Alejandro Fuentes González'
Asunto: RE: [ODBC] Problems with ORACLE

Thank you so much for forwarding this information. The fix for them is simple.
Have the ODBC translation layer either always use double quotes or never use
them. If you have a TAR number from oracle I would appreciate knowing it so I
can find out when this problem is resolved. As it turns out, the future project
I was exploring the Oracle to PostgreSQL communications link for is happening
now. ODBC would certainly make things easier.

Thanks again.
-- dave

-----Original Message-----
From: Alejandro Fuentes González [mailto:alejandro(dot)fuentes(at)SSP(dot)DF(dot)GOB(dot)MX]
Sent: Friday, August 01, 2003 2:33 PM
To: Schleis, David; 'pgsql-odbc(at)postgresql(dot)org'
Subject: RE: [ODBC] Problems with ORACLE

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(at)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(at)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(at)mail(dot)slh(dot)wisc(dot)edu]
Enviado el: Martes, 29 de Julio de 2003 09:01 a.m.
Para: 'pgsql-odbc(at)postgresql(dot)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

-----Original Message-----
From: Alejandro Fuentes González [mailto:alejandro(dot)fuentes(at)SSP(dot)DF(dot)GOB(dot)MX]
Sent: Monday, July 28, 2003 7:08 PM
To: pgsql-odbc(at)postgresql(dot)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

+++++++++++++++++++++++++++++++++++++++++++++

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org]
On Behalf Of Ludek Finstrle
Sent: Thursday, June 22, 2006 11:07 AM
To: LLC
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Oracle Heterogenous Services Error

> The mylog output is listed below:
>
> Thanks for your help with this...
>
>
> [4168][SQLDriverConnectW][4168]PGAPI_DriverConnect: entering...\par
> [4168]**** PGAPI_DriverConnect: fDriverCompletion=0,
> connStrIn='DSN=KAM_FINAL;UID=PGADMIN;PWD=xxxxxxx;'\par

As I said before. psqlODBC get PGADMIN instead of pgadmin. There are two ways:
1) say Oracle to don't uppercase the username
2) create PGADMIN user in postgresql backend - to create this
user you have to spcify the username in double quotes
(somethink like this: create user "PGADMIN").

It isn't psqlODBC problem.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend