Re: Issue with Oracle Database Gateway for ODBC and Unicode

From: Carlos Muñoz Juste <cmunoz(at)es(dot)lladro(dot)com>
To: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org, pgsql-odbc-owner(at)postgresql(dot)org
Subject: Re: Issue with Oracle Database Gateway for ODBC and Unicode
Date: 2013-05-13 15:49:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-odbc

Hi Heikki,

I opened the service request with Oracle and they solved my problem. It is
indeed a bug on the gateway for ODBC. People who have access to Oracle
Support can check note 1468941.1 .

If I understood correctly, the problem is that the gateway by default does
not convert from nchar to char and back. They told me this will be fixed
on patchset, but in the meantime there is the following
workaround to activate this type of conversion:

1. Please get your DG4ODBC class using this select statement executed as

select fds_class_name from HS_FDS_CLASS;
=> you should see a class like: ODBC11.

2. When you now check out the to_nchar capability you'll see it is turned
off (=0)

select * from hs_class_caps where upper(CAP_DESCRIPTION) like '%NCHAR%'
=> relevant to_char capability is 564:

564 TO_NCHAR(op1) 0
3. We now need to modify this capability and turn it on:

'ODBC11.', 564,131071, NULL, NULL);
SIDE NOTE: Please make sure you did this capability update as sysdba user
and please do not forget the commit.
4. When executing the select from setp 2 again you now should get for the
context_translation 131071 instead of 0:

564 TO_NCHAR(op1) 131071

- change "ODBC11." to whatever is returned by your query.

The note then says to set these values in the gateway init file -

HS_NLS_NCHAR=UCS2 => should already be in the gateway init file
HS_LANGUAGE=american_america.al32utf8 => needs to be changed as only in
unicode mode the gateway will be able to handle the nchar columns

An that's it. The cast() is not necessary anymore

De: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
Para: Carlos Muñoz Juste <cmunoz(at)es(dot)lladro(dot)com>,
cc: pgsql-odbc(at)postgresql(dot)org
Fecha: 13/05/2013 12:31
Asunto: Re: [ODBC] Issue with Oracle Database Gateway for ODBC and Unicode
Enviado por: pgsql-odbc-owner(at)postgresql(dot)org

On 13.05.2013 12:21, Carlos Muñoz Juste wrote:
> We have noticed that this only happens when the column in the where
> is of type character varying, due to the fact that the Oracle Database
> Gateway for ODBC returns all character varying columns in Postgres as
> 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
> Japanese, Russian and Chinese characters. It does not happen either when
> we access the Postgres database through the psqlodbc Unicode driver
> 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
> 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
> 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

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 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

In response to

Browse pgsql-odbc by date

  From Date Subject
Next Message Hiroshi Inoue 2013-05-13 16:26:21 Re: Re: Are UseDeclareFetch and UseServerSidePrepare mutually exclusive?
Previous Message ljwilson 2013-05-13 14:13:22 Re: Are UseDeclareFetch and UseServerSidePrepare mutually exclusive?