Is there an easy way to convert HTTP_ACCEPT_LANGUAGE to Oracle NLS_LANG settings?

Question

When adding internationalisation capabilities to an Oracle web application (build on mod_plsql), I'd like to interpret the HTTP_ACCEPT_LANGUAGE parameter and use it to set various NLS_* settings in the Oracle session.

For example:

HTTP_ACCEPT_LANGUAGE=de

alter session set nls_territory=germany;
alter session set nls_lang=...

However, you could get something more complicated I suppose...

HTTP_ACCEPT_LANGUAGE=en-us,en;q=0.5

How have folks tackled this sort of thing before?

EDIT - following on from Curt's detailed answer below

Thanks for the clear and detailed reply Curt. I didn't really make myself clear though, as I was really asking if there were any existing Oracle widgets that handled this.

I'm already down the road of manually parsing the HTTP_ACCEPT_LANGUAGE variable and - as Curt indicated in his answer - there are a few subtle areas of complexity. It feels like something that must have been done many times before. As I wrote more and more code I had that sinking "I'm reinventing the wheel" feeling. :)

There must be an existing Oracle approach for this - probably something in iAS??

EDIT - stumbled across the answer

While looking for something else, I stumbled across the UTL_I18N package, which does exactly wham I'm after:

http://stackoverflow.com/questions/895839/is-there-an-easy-way-to-convert-httpacceptlanguage-to-oracle-nlslang-settings/1820331#1820331

Answer 1

Sure, and it's not too tough, if you break up the problem properly and don't get to ambitious at first.

You need, essentially, two functions: one to parse the HTTP_ACCEPT_LANGUAGE and produce a language code, and one to take that and generate the appropriate set commands.

The former can get pretty sophisticated; if you're given only 'en', you probably want to generate 'en-us', you need to deal with chosing one of multiple choices when nothing matches perfectly, you need to deal with malformed header values, and so on. Don't try to tackle this all at once: just do something very simple at first, and extend it later.

The same more or less goes for the other half of it, generating the set commands, but this is pretty simple in and of itself anyway; it's really just a lookup function, though it may get a bit more sophisticated depending on what is provided to it.

What will really make or break your programming experience on something like this is your unit tests. This is an ideal problem for unit testing and test-driven development. Unit tests will make sure that when you change things, old functionality keeps working, and make it easier to add new functionality and fix bugs, because you just add another test and you have that to guide you from that point on. (You'll also find it easier to do a complete rewrite of one of the functions if you find out you've gone terribly wrong at some point, because you can easily confirm that the new version isn't breaking anything.)

How you do unit testing in your environment is probably a bit beyond the scope of this question, but let me add a few hints. First, if there's a unit test framework ("pl-sql-unit?") available for your environment, that's great. If not, don't panic. You don't need anything sophisticated: just a set of inputs and expected outputs, and a way to run them through the function and either say "all OK!" or show any incorrect results. You can probably write a single, simple PL/SQL function that reads the inputs and expected outputs from a table and does this for you.

Answer 2

Finally stumbled across the answer. The Oracle package UTL_I18N contains functions to map from the browser language codes to Oracle NLS settings:

utl_i18n.map_language_from_iso;
utl_i18n.map_territory_from_iso;

The mapping doesn't seem to cope very well with multi-language settings, e.g. en-us,en;q=0.5, but as long as you just use the first 5 characters the functions seem to work ok.

HTTP_ACCEPT_LANGUAGE: ar-lb,en-gb;q=0.5
v_language: 
v_territory: 

HTTP_ACCEPT_LANGUAGE: ar-lb
v_language: ARABIC
v_territory: LEBANON