In Oracle E-Business Suite, 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. Usually views with a condition of: LANGUAGE = USERENV (‘LANG’)in the where clause will not return data if the NLS LANGUAGE is not setup correctly.
Set USERENV(‘LANG’) in SQL*Plus
Check what language is set, using following query you can set the NLS LANGUAGE as follows:
SELECT USERENV(‘LANGUAGE’) “Language” FROM DUAL;
Set it to ‘AMERICAN’ using following alter command, you definitely get
ALTER session SET nls_language=’AMERICAN’
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
Set USERENV(‘LANG’) For TOAD
Access NLS Parameters, from the Database menu, -> select Administer| -> NLS Parameters
Set the NLS_LANGUAGE to ‘AMERICAN’
There some times org specific tables for that you need to setup org_id, you can use any of the following