grails 3 oracle datasource for russian text

Question

I am working on a grails application which connects to oracle database. It works fine except when there are any russian text in the db. For russian text, I get inverted ?. There are some approach in other questions for mysql but I could not find datasource configuration for oracle. Any help here will be great.

EDIT - The russian text is stored in varchar2 datatype. and here is the query -

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%SET'; NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

Please run this query: SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%SET' and append it's result to the question as a text, not a bitmap. Please also explain which datatype is used in your tables to store a russian text, is it VARCHAR2 or NVARCHAR2 or CLOB or NCLOB ? Currently there is to few information in the question to diagnose this problem. — krokodilko
WE8MSWIN1252 is CP-1252 code page, it does not support Russian cyrillic characters (like консэквюат etc), see this link to know supported characters by this code page: en.wikipedia.org/wiki/Windows-1252. You need to use NVARCHAR2 instead of VARCHAR2 datatype to store such a text, or migrate (convert) your database to a proper codepage (but it's not an easy taks). — krokodilko

krokodilko krokodilko · Accepted Answer · 2016-07-06T13:43:56

What does a result of the below query mean ?

SELECT * FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER LIKE '%SET';
PARAMETER                      VALUE                        
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16                     
NLS_CHARACTERSET               WE8MSWIN1252

This means that:

AL16UTF16 encoding (a 16-bit encoding of Unicode) is used to encode data in NVARCHAR2 variables, table columns and literals
WE8MSWIN1252 (an 8-bit ASCII based Oracle Database character set) is used to encode data in VARCHAR2 variables, table columns and literals

Unfortunately WE8MSWIN1252 is CP-1252 code page, which does not support Russian cyrillic characters (like консэквюат etc).
See this link to know supported characters by this code page: en.wikipedia.org/wiki/Windows-1252.

Someone during installation has not considered Russian characters and probably choosen a bad code page. The documentation lists code pages that support Russian language (see Table A-13 Languages and Character Sets Supported by LCSSCAN and GDK):
https://docs.oracle.com/database/121/NLSPG/applocaledata.htm#NLSPG585

Russian

AL16UTF16, AL32UTF8, CL8ISO8859P5, CL8KOI8R, CL8MSWIN1251, RU8PC866, UTF8

You can observe an effect of using this code page in the following examples:

SELECT * FROM NLS_DATABASE_PARAMETERS 
WHERE PARAMETER LIKE '%SET'; 
PARAMETER                      VALUE                        
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16                     
NLS_CHARACTERSET               WE8MSWIN1252 


select 'консэквюат' x, n'консэквюат' y from dual;
X          Y        
---------- ----------
¿¿¿¿¿¿¿¿¿¿ консэквюат

and also:

create table test(
  v1 varchar2(100),
  v2 nvarchar2(100)
);

insert into test( v1, v2 ) values ('консэквюат', 'консэквюат' );
insert into test( v1, v2 ) values (n'консэквюат', n'консэквюат' );

select * from test;
V1              V2            
--------------- ---------------
¿¿¿¿¿¿¿¿¿¿      ¿¿¿¿¿¿¿¿¿¿     
¿¿¿¿¿¿¿¿¿¿      консэквюат

and also:

create FUNCTION function1( par varchar2 ) return varchar2 
IS
BEGIN 
  return par;
END;
/
create FUNCTION function2( par nvarchar2 ) return varchar2 
IS
BEGIN 
  return par;
END;
/
create FUNCTION function3( par varchar2 ) return nvarchar2 
IS
BEGIN 
  return par;
END;
/
create FUNCTION function4( par nvarchar2 ) return nvarchar2 
IS
BEGIN 
  return par;
END;
/

select function1( n'консэквюат' ) x1,
       function2( n'консэквюат' ) x2,
       function3( n'консэквюат' ) x3,
       function4( n'консэквюат' ) x4
from dual;

X1              X2              X3              X4            
--------------- --------------- --------------- ---------------
¿¿¿¿¿¿¿¿¿¿      ¿¿¿¿¿¿¿¿¿¿      ¿¿¿¿¿¿¿¿¿¿      консэквюат

As you see from the above examples, only NVARCHAR2 values are properly stored and converted, any use of VARCHAR2 value causes a conversion to WE8MSWIN1252 code page and all Russian characters are lost.

What you can do in the current situation:

Use NVARCHAR2 instead of VARCHAR2 datatype in all tables and stored procedures - you need to rewrite all database procedures, functions, triggers etc.
Migrate your database to another character set that supports Russian language. This is not easy task and it's beyound this question, for details refer to the documentation: https://docs.oracle.com/database/121/NLSPG/ch11charsetmig.htm#NLSPG011 and disscuss this topic with your DBA

grails 3 oracle datasource for russian text

1 Answers