Re: Issue with Oracle Database Gateway for ODBC and Unicode

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 11.2.0.4, 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
sysdba:

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

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%'
and FDS_CLASS_NAME ='ODBC11.2.0.2.0_0008';
=> relevant to_char capability is 564:

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

exec DBMS_HS.ALTER_CLASS_CAPS('ODBC11.2.0.2.0_0008' , 564,
'ODBC11.2.0.2.0_0008', 564,131071, NULL, NULL);
commit;
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.2.0.2.0_0008" 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
correctly

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

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