Updating NLS_LANG For A Windows Client Installation

Usually you'd expect your Client and Server environments to be in sync but there are specific issues when it isn't the case and this can lead to unexpected conversions when inserting text. We suffer from this issue as our server environment is in "American" whereas our clients are usually in "British".

It doesn't cause massive issues but occasionally, especially with Oracle e-Business Suite, this can catch us out (typically it's due to it trying to look up a UK-English translation of something in an API call that doesn't exist).

Here's a quick guide to update your client-side environment.

First of all open "Registry Editor" (type "regedit" under Start Menu > Run) and search for NLS_LANG. you'll see something like;

I've highlighted the NLS_LANG entry, as you can see in the image it's currently set to;

AMERICAN_AMERICA.WE8MSWIN1252

From here it's just a quick matter of double-clicking the entry and updating it.

NOTE: It's always worth doing a "Find Again" to get the next instance of NLS_LANG just in case you have multiple clients installed.

Oracle EBS: Initialising the APPS Environment in PL/SQL

How to set your user, responsibility, etc in PL/SQL to work with APPS functions

This blog post covers a fairly simple PL/SQL block that allows you to initialise your APPS environment from within PL/SQL allowing you to do things like run concurrent requests.

Language (NLS_LANGUAGE and NLS_TERRITORY)

The most important thing to start with is to make sure that your language and the current APPS configured languages and territory are the same in your session as they are on the server. You only need to do this step if you are looking onto a server configured with a different language. For example here in the UK our server is configured as AMERICAN.

To alter your session in PL/SQL you need to use EXECUTE IMMEDIATE;

execute immediate 'alter session set NLS_LANGUAGE = AMERICAN';
execute immediate 'alter session set NLS_TERRITORY = AMERICA';

If you want to see what your current settings are you can query FND_GLOBAL.NLS_LANGUAGE - in our case this turned out to either be GB or null.

Initialising Oracle EBS Environment

Clearly this issue has been around for a long time as Oracle provide a handy API in order to setup the environment. This API is part of the FND_GLOBAL package and is called APPS_INITIALIZE (note the US spelling). This API takes the following parameters;

USER_ID - The ID of the user (from FND_USER)
RESP_ID - The ID of the responsibility (from FND_RESPONSIBILITY)
RESP_APPL_ID - The ID of the application (also from FND_RESPONSIBILITY, APPLICATION_ID column)
SECURITY_GROUP_ID - This has a default value and in most cases you won't need to change it
SERVER_ID - Same with this

Once you have the correct values you can execute the call using the PL/SQL;

APPS.FND_GLOBAL.APPS_INITIALIZE(
  user_id      => v_UserId,
  resp_id      => v_RespId,
  resp_appl_id => v_RespAppId);

If successful you can then query values in FND_GLOBAL.

NOTE: It's worth probably saying that you just need to Initialize the environment - it's quite possible if you have an environment initialised as a lowly user you would still be able to pro grammatically run System Administrator Concurrent Requests with it.