ver. 6.30 and ORA-12704: character set mismatch

ver. 6.30 and ORA-12704: character set mismatch

Postby aldare » Tue 24 May 2011 08:28

Hello,

we have upgraded to ver. 6.30.160 and some of our linq entity queries ceased to work properly. We are getting the Oracle error message "ORA-12704: character set mismatch" which was never seen with previous versions (ver. 6.10 and previous).

After some analysis we've found the problem is the function call to TO_NCHAR() in the translated SQL query. After manual replacement by TO_CHAR() the query works without problems.

The linq looks like this :

... where entity.property == "A" ...

What has changed in the SQL code generation that could cause these problems? Thank you for any help
Last edited by aldare on Tue 24 May 2011 10:57, edited 1 time in total.
aldare
 
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Postby AnssiA » Tue 24 May 2011 10:47

We are having same kind of problems after update to 6.30 in one query:

...
...
Contact = o.SalesContactPerson == null ? null : o.SalesContactPerson.FirstName + " " + o.SalesContactPerson.LastName
...

That line result to: "ORA-12704: character set mismatch"
AnssiA
 
Posts: 6
Joined: Wed 04 May 2011 06:43
Location: Finland

Postby zmeskal » Tue 24 May 2011 11:54

I have a same issue - ORA-12704: character set mismatch after update to version 6.30.

If I use in the generated SELECT function TO_CHAR instead of TO_NCHAR then SELECT works fine. I don't have any national datatype (nchar, nvarchar...) in tables but dotConnect using TO_NCHAR function in the CAST command.

There is probably relationship with the Oracle document ID 742684.1 on Metalink.

My database parameters:
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CURRENCY = $
NLS_ISO_CURRENCY = AMERICA
NLS_NUMERIC_CHARACTERS = .,
NLS_CHARACTERSET = AL32UTF8
NLS_CALENDAR = GREGORIAN
NLS_DATE_FORMAT = DD-MON-RR
NLS_DATE_LANGUAGE = AMERICAN
NLS_SORT = BINARY
NLS_TIME_FORMAT = HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT = DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT = HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT = DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY = $
NLS_COMP = BINARY
NLS_LENGTH_SEMANTICS = CHAR
NLS_NCHAR_CONV_EXCP = FALSE
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_RDBMS_VERSION = 11.2.0.1.0
zmeskal
 
Posts: 11
Joined: Sun 04 Apr 2010 06:18

Postby Shalex » Wed 25 May 2011 11:00

We cannot reproduce the problem at the moment. Could you please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment? This will speed up our investigation.
Shalex
Devart Team
 
Posts: 7533
Joined: Thu 14 Aug 2008 12:44

Postby zmeskal » Wed 25 May 2011 14:02

I'm have send to you database schema script and generated select by email [Devart #29269].
zmeskal
 
Posts: 11
Joined: Sun 04 Apr 2010 06:18

Postby Shalex » Thu 26 May 2011 12:10

zmeskal,

We still cannot reproduce the problem. We have asked for an additional information by e-mail.

Best regards
Shalex
Devart Team
 
Posts: 7533
Joined: Thu 14 Aug 2008 12:44

Postby aldare » Fri 27 May 2011 08:17

It seems we've found the solution. After re-generation of the model with actual version everything works as expected. The problem is how the generator generated the edmx file. Our property causing the problems was with unicode="true" and now it is "false".
The change seems to be in the version 6.10.
aldare
 
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Postby zmeskal » Fri 27 May 2011 10:04

But this is only partially success (or temporary workaround) because we in the future may need some properties with property unicode = true.
To Devart support: Can you reproduce the bug this with Unicode = true?
zmeskal
 
Posts: 11
Joined: Sun 04 Apr 2010 06:18

Postby aldare » Fri 27 May 2011 13:10

My opinion is there was some kind of an error in ver. 5.70.
For non-unicode columns (char in our case) there was unicode = "true" in the generated edmx file. Becuse the sql query generated from our linq query didn't contain any calls to to_nchar(), this was not of a problem. Everything worked well. But it seems that ver 6.10 changed the generation of the same linq query and now it contains call(s) to to_nchar() which is causing the problems.

I would say NLS_CHARACTERSET is not what matters here, the important thing is whether the column is unicode column (nchar, nvarchar...)
Can anyone from devart team confirm this theory?
Thank you
aldare
 
Posts: 17
Joined: Mon 14 Dec 2009 08:55

Postby Shalex » Fri 27 May 2011 14:18

aldare,

zmeskal has already sent us the DDL script of your VKLADY_OWNER schema. Assuming that we created a model from your database, please post here a test Entity SQL or LINQ query that generates erroneous SQL with dotConnect for Oracle v 6.30.160. Otherwise, send us a small complete test project to reproduce the problem in our environment.

Best regards
Shalex
Devart Team
 
Posts: 7533
Joined: Thu 14 Aug 2008 12:44

Ran Into Same Issue

Postby yensid21 » Wed 01 Jun 2011 16:48

I ran into the same issue, however when I set the Unicode property to false, it fixed the problem.
yensid21
 
Posts: 11
Joined: Fri 17 Sep 2010 16:16

Re: Ran Into Same Issue

Postby zmeskal » Wed 01 Jun 2011 18:20

yensid21 wrote:I ran into the same issue, however when I set the Unicode property to false, it fixed the problem.

Thanks for info. Can I set unicode=false for column type NCHAR, NCLOB, NVARCHAR, ... in database with Encoding UTF-8?
zmeskal
 
Posts: 11
Joined: Sun 04 Apr 2010 06:18

Postby AndreyR » Thu 02 Jun 2011 12:52

Zmeskal, could you please try to regenerate your entity model from scratch and try to reproduce the problem? There was a problem in the Unicode facet setting that was fixed in recent builds.
If this advice isn't helpful, please send us the LINQ (or Entity SQL) query to reproduce the problem.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby zmeskal » Fri 03 Jun 2011 16:08

AndreyR, when I regenerated model then everything works OK. But I haven't used national datatype (NVARCHAR etc.) at the time. My question is about behavior with NVARCHAR datatype - must be unicode=true for this datatype? If yes then there is same problem with ora-12704 or not?
zmeskal
 
Posts: 11
Joined: Sun 04 Apr 2010 06:18

Postby AndreyR » Wed 08 Jun 2011 07:49

Sorry for the delay. There should not be any problems in this case.
However, we are unable to cover all the situations with our tests.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle