Set USERENV(‘LANG’) for SQL Developer

As SQL Developer doesn’t require an Oracle client, it doesn’t look at the Oracle registry settings to get your default NLS settings (where TOAD looks) – it picks up defaults from the OS (i.e. Regional and Language Settings on Windows).
To fix this (i.e.to make USERENV (‘lang’) return US), change your SQL Developer NLS settings (Tools > Preferences > Database > NLS Parameters) to have Language = AMERICAN and Territory = AMERICA

For Toad, Access NLS Parameters, from the Database menu, select Administer|NLS Parameters

There some times org specific tables for that you need to setup org_id, you can use any of the following

fnd_global.apps_initialize
OR
fnd_client_info.set_org_context
OR
dbms_application_info.set_client_info

For oracle E-Business, and if there are multiple languages installed, and if you are not able accesses data from tables like fnd_lookup_values_vl or any other VL tables, then check what language is set, using following quiery you can check

SELECT USERENV(‘LANGUAGE’) “Language” FROM DUAL;

Set it to ‘AMERICAN’ using following alter command, you definitely get

alter session set nls_language=’AMERICAN’

Leave a Reply