illegal mix of collations
Posted by: Dale Greenlee
Date: April 16, 2009 10:42AM

Yeah I know this is an :"old topic" but I do not seem to be able to find a solution in the conversation yet.

I, too, am getting the illegal mix of collation errors. My database is using the character set utf8 with collation utf8_general_ci. I have checked and all the markers indicate that all my tables and text columns are correct with this.

But I have a udf in which I take a text value from one table select from another table a an Integer column where a different text column is equal to the first value. And I get this error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and(utf8_unicode_ci,IMPLICIT) for operation "="

The offending line is:

SELECT StateID into lvStateID FROM States WHERE (CountryID = pCountryID) AND (Code = pStateCode)

(If I remove this line I do not get an error)

pStateCode and pCountryID are parameters TEXT and INT respectively. pStateCode is the value retrieved from the other table and is a text 2 characters long. If I replace pStateCode with the string "OR" [SELECT StateID into lvStateID FROM States WHERE (CountryID = pCountryID) AND (Code = "OR")] I also do not get an error.

show full columns from stores results in this for Code:
|Code |varchar(2) |utf8_general_ci |NO | | NULL |...

same for the source table for pStateCode gives:
|custate |varchar(2) | utf8_general_ci |YES | |NULL |...

What am I missing to get this to read the collations correctly?

Options: ReplyQuote


Subject
Views
Written By
Posted
illegal mix of collations
4943
April 16, 2009 10:42AM
2569
April 19, 2009 12:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.