set locale on Oracle connection

In my company's product, we retrieve results a page at a time from the database. Because of this all filtering and sorting must be done on the database. One of the problems is coded values. For filtering and sorting to work properly, the code values need to be translated to locale specific labels in the query.

My plan is to use a table similar to the following: t_code_to_label ( type varchar2(10), locale varchar2(10), code varchar2(10), label varchar2(50) ) The first three columns are the primary (unique) key.

When using this table, you would see something like this

select ent.name, ent.ent_type, entlabel.label as ent_type_label from t_entitlement开发者_运维技巧s ent join t_code_to_label entlabel on entlabel.type='entlabel' and entlabel.locale=currentLocale() and entlabel.code=ent.ent_type

The problem is that currentLocale() is something that I made up. How can I on the Java side of a JDBC connection do something to set the locale for the Connection object that I have that I can read on the Oracle side in a simple function. Ideally this is true locale support by Oracle but I can't find such a thing.

I am using Oracle 10g and 11g.

Are you talking about the NLS_LANGUAGE setting of the Oracle database? Would you (from the client side) like to dictate the usage of a particular NLS_LANGUAGE by Oracle database?

Then maybe this would work: Set Oracle NLS_LANGUAGE from java in a webapp

If you want an "all american" session you could do you could do something like:

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' 
                  NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' 
                  NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' 
                  NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' 
                  NLS_SORT= 'BINARY'