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

Answer 1

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:

1. Use NVARCHAR2 instead of VARCHAR2 datatype in all tables and stored procedures - you need to rewrite all database procedures, functions, triggers etc.
2. 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