Search on unicode strings on NVarchar2 fields in ORACLE fail using ADOQuery

979724

Db parameters in oracle
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_SORT BINARY
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.2.0

If ADOQuery is used to connect to ORACLE database using Oracle OLEDB provider search on unicode strings for NVARCHAR2 fields fails

In the Oracle (11g) database, there is a table like

create table unicodetest
(Code Number, Name NVarchar2(100))

Now I have inserted data into it (done through sql developer)

insert into unicodetest
values (1, N'ユニコード')

Now from delphi (XE2) application, I used TADOQuery to connect to ORACLE database using ORAOLEDB provider.

Now if I search for unicode string using following query

select * from unicodetest
where
NAME like N'%ユニコード%'

It returns 0 records

Even without using N it fails.

Can you suggest how could this be solved?