ver. 6.30 and ORA-12704: character set mismatch

aldare · Post by **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

AnssiA · Post by **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"

zmeskal · Post by **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

Shalex · Post by **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.

zmeskal · Post by **zmeskal** » Wed 25 May 2011 14:02

I'm have send to you database schema script and generated select by email [Devart #29269].

Shalex · Post by **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

aldare · Post by **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.

zmeskal · Post by **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?

aldare · Post by **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

Shalex · Post by **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

yensid21 · Post by **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.

zmeskal · Post by **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?

AndreyR · Post by **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.

zmeskal · Post by **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?

AndreyR · Post by **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.

Devart Forums

ver. 6.30 and ORA-12704: character set mismatch

ver. 6.30 and ORA-12704: character set mismatch

Ran Into Same Issue

Re: Ran Into Same Issue