Re: Issue with Oracle Database Gateway for ODBC and Unicode

On 13.05.2013 12:21, Carlos Muñoz Juste wrote:
> We have noticed that this only happens when the column in the where clause
> is of type character varying, due to the fact that the Oracle Database
> Gateway for ODBC returns all character varying columns in Postgres as type
> NVARCHAR2. If we issue the query like this, then the where clause is
> passed to Postgres:
>
> select * from "dps_user"@pg where "id" = cast('32422' as nvarchar2(40))
>
> This behavior does not happen when we used the psqlodbc ANSI driver, but
> unfortunately the Unicode driver is a must because both databases contain
> Japanese, Russian and Chinese characters. It does not happen either when
> we access the Postgres database through the psqlodbc Unicode driver from,
> for example, Microsoft Query, so it has something to do with the Oracle
> Database Gateway.
>
> However, I thought I would post my problem here, just in case you ran into
> this problem or heard of someone who did. Having to use the cast as
> nvarchar2 works, but it is very cumbersome. I wonder if there would be a
> way to configure the psqlodbc driver so that a varchar2 column is returned
> instead.

To be precise, the driver returns the column type to be the ODBC type
SQL_WVARCHAR. Oracle Database Gateway must be translating that into
NVARCHAR2. And you would like the column type to be returned as
SQL_VARCHAR instead, which the Gateway will hopefully translate into
VARCHAR2.

As a matter of fact, there is a setting to do that. It's a bit hidden
away, it's the "Extra Opts" value 0x4. See
http://psqlodbc.projects.pgfoundry.org/docs/config.html. I think it's
set in the connection string or ini file as "AB=0x4". There should also
be a field for it somewhere in the configuration GUI

> I am going to open a service request with Oracle to see if they can help
> me on the gateway end.

Yeah, seems like a bug or at least a missing feature in the Gateway.

- Heikki