Oracle Views return no data due to NLS LANGUAGE Settings

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

fnd_global.apps_initialize

OR fnd_client_info.set_org_context

OR dbms_application_info.set_client_info

Leave a comment

Your email address will not be published. Required fields are marked *