Oracle Unicode Support in 10g and 11g Differences

Question

I am running into a problem that I have not yet been able to explain. I have code that takes a number, gets the national character code for the number using NCHR, and then performs a RAWTOHEX conversion on it.

It worked in 10g for years. When we upgraded to 11g it started returning different values. I boiled it all down to a few statements, and created a demonstration script:

SET SERVEROUTPUT ON;
DECLARE
  rawVar RAW(2000);
  nVar NVARCHAR2(1000);
BEGIN
  nVar :=  NCHR(1112);
  SELECT RAWTOHEX(nVar) INTO rawVar FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('rawVar: ' || rawVar);
END;
/

When executed in 10g, the ouptut is "0458". In 11g (from the same computer and using the same Oracle client software) the output is "00040058". The upstream process that relies on the output is expecting "0458".

Interestingly (to me), if I change the definition of nVar to be an VARCHAR2 instead of an NVARCHAR2, I get "0458" as the output on 11g.

Can someone please help to explain why the results are different? I have searched Oracle's release notes and support system, but have not found any answers.

Many thanks in advance.

Answer 1

Hi elmnoise,

Are the two databases using the same character set ?

Could you run this query in both instances:

select value 
  from nls_database_parameters 
 where parameter='NLS_NCHAR_CHARACTERSET';

CHR and NCHR functions will only give the same result if the databases have the same character set.

Answer 2

RAWTOHEX returns a character value, but you are selecting it into a RAW, so there will be some implicit conversion there. Then you are trying to use DBMS_OUTPUT which will need to implicitly convert that RAW variable into a string.

Potentially you can deal with a RAW value (ie bytes) or a Character value (text which may get converted/translated depending on character set / language settings) or a hex representation of the bytes in the the string. Which of those do you have in the database, and which do you want to return to the application ? Then just do that ONE conversion and do it explicitly.

Answer 3

This is a horrible bug in Oracle 11 Change nVar := NCHR(1112); to nVar := CHR(1112 using nchar_cs); and things will work again. These expressions should be identical, according to Oracle docs. In Oracle 10 this is true, but not in 11.