How to sort results case-insensitive in Oracle SQL?
The simple answer is let the database do it. That way the way the bind variable is put into lower case will be consistent with the way the column value is put into lowercase.
String sql = "select * from Person where lower(name) = lower(?)";
... but how do I know what Locale the database is using? Can I check this programmatically ...
There doesn't appear to be a portable (database independent) way to do this, but you can apparently use the following query to get the charset used by an Orable database:
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';This page gives more details.
As for actually doing the comparison, you would be best off (*) letting the database take care of the lower-casing, as @Gary suggests. The JDBC driver will take care of converting Java (UTF-16) Strings into whatever the database is using.
(* In fact, I don't think you have much choice, unless you are prepared to wear the cost of storing mixed-case and lower-case copies of all queriable strings in the database.)