I have a query statement that uses "LIKE" operator to retrieve search result from Oracle database that contains Arabic & English data. It works like a charm with English characters after the "LIKE", but it retrieves 0 records with Arabic characters.
here is simplified code of mine:
String whereSt = " WHERE Arb_NAME LIKE '%محمد' "; //Generated from caller method
String selectSt = " Select * from Users " + whereSt;
DynamicDBTable dDBT = new DynamicDBTable(this.dbConn, TableSpace.Users);
DataSet result = dDBT.FetchData();
Oracle Data Provider inherits character set from
NLS_LANG value. Set your
NLS_LANG value to a character set which supports Arabic characters, e.g.
NLS_LANG=ARABIC_BAHRAIN.WE8MSWIN1256 or even better
You can set
NLS_LANG either as environment variable or in your Registry at
HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32-bit Client), resp.
HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 64-bit Client).
NLS_LANG value it not set at all then Oracle defaults it to
AMERICAN_AMERICA.US7ASCII, i.e. no support for Arabic characters.
SQL Developer uses JDBC driver to connect to Oracle but JDBC does not use NLS_LANG settings, that's the reason why it works on SQL Developer. See Database JDBC Developer's Guide - Globalization Support
Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.
Apart from that you should prefer bind variables instead of hard-coded strings in WHERE condition.