No rows are returned when looking/querying VIEWS on an Oracle Application (Ora Apps) Instance (4327253)

When viewing data for VIEWS on an Oracle Applications instance, TOAD does not display the data.

For example, assume that the user has a standard Ora Apps view:

oe_order_headers_v

If the user tries to view the data either through the Schema Browser | Data tab or by querying the view in the SQL Editor, the data is not displayed. No rows are returned.

This is not actually a TOAD issue but an Oracle one. Some of the ORA APPS view's use LANG settings in the WHERE clause. Basically the data is stored with a LANGUAGE setting; by default this is normally 'US'.

These views check the client LANGUAGE setting. If the client LANGUAGE is set to anything other than the data LANGUAGE stored in the view's TABLE, then it will not display the data. For example:

If the client National Language Setting (NLS) were set to "ENGLISH_UNITED KINGDOM.WE8MSWIN1252," then the LANGUAGE would be 'GB.'

Verify the  LANGUAGE setting by running the following statement:

SELECT USERENV('LANG') FROM DUAL;

Based on this, if data is stored with a LANGUAGE of 'US' , then the language setting of the client has to be altered with the following methods:

RESOLUTION 1:

The first way would need to done every time a new session is created. Run the following in the Sql Editor of TOAD, to set the LANGUAGE to US use the following:

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

Data will be displayed for these views for any rows that have 'US' in the lang column. The settings remains for this session, and a new session will need to have the settings changed in order for data to be displayed.

RESOLUTION 2:

The second method by changing the NLS_LANG value, it allows a permanent system wide change and allows to all Oracle sessions.

a) Go to Start | Run | Regedit
b) Drill down to HKLM | Software | Oracle | HOMEX (Where X is the currently used Home)
c) Double click NLS_LANG, change the NLS_LANGUAGE and the NLS_TERRITORY; you do not need to change the Character Set.

For example:
If the NLS_LANG value were set to "ENGLISH_UNITED KINGDOM.WE8MSWIN1252," then change it to "AMERICAN_AMERICA.WE8MSWIN1252."
d) Close Regedit and restart TOAD for the changes to take effect.

Disclaimer : Quest does not provide support for problems that arise from improper modification of the registry. The Windows registry contains information critical to your computer and applications. Make sure you back up the registry before modifying it. For more information on the Windows Registry Editor and how to back up and restore it, refer to Microsoft Article ID 256986 "Description of the Microsoft Windows registry": http://support.microsoft.com/default.aspx?kbid=256986