Set Oracle NLS_LANGUAGE from java in a webapp

Question

My understanding of JDBC is that it automatically sets the Oracle NLS_LANGUAGE/NLS_TERRITORY session parameters based on the default Locale of the JVM. This is handy for a stand-alone swing app, but seems useless for a java webapp. The only solution I can come up with is to specifically set the session parameters right before actually doing a database query, something similar to:

Connection c = // However you get it.
Statement s = c.createStatement();
s.execute("alter session set NLS_LANGUAGE = 'SPANISH'");
// Do actual query here

My questions:

1. Is this the best way to set the Oracle language/country parameters from a webapp?
2. Since the Oracle parameters take language names rather than codes, is there a mapping from java/ISO language codes to Oracle language names? For example, can I use Locale.getDisplayLanguage() and be safe?

Answer 1

If you're using connection pooling, it's the best way to make sure that Johnny's results are in American and Johann's results are in German.

You could store the user's preferred NLS value for LANGUAGE, TERRITORY, CHARACTERSET, and SORT, using V$NLS_VALID_VALUES as a data source, assuming you store login/user information.

Answer 2

Can depend on your architecture.

Some places opt to have multiple geographically local web-apps against a single database (ie one in the France, one in the UK, one in Spain).

Or you pull data (eg dates, numbers) in 'computer' format from the database and have the web-app convert it to the end-user's preferences (or possibly based on browser information if you don't have user logins).

Or you have separate connection pools for different 'territories'.

Bear in mind that queries changing territories can affect index usage and sorting.