As Oracle 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 the Oracle function, USERENV (‘lang’), and return language as US, change your SQL Developer NLS settings (Tools > Preferences > Database > NLS Parameters) to have Language = AMERICAN and Territory = AMERICA
For Toad you need to go to -> Access NLS Parameters, from the Database menu, select Administer|NLS Parameters
Setting the organization/profile on the SQL session in a Multi Org instance
There some times org specific tables for that you need to setup org_id, you can use any of the following
Oracle 11i script to set the profile for a responsibility associated to a user
1
2
3
4
5
6
| BEGIN fnd_global.apps_initialize (user_id => 101, resp_id => 123, resp_appl_id => 234 );END; |
OR
Oracle 11i script to set the operating unit
1
2
3
| BEGIN fnd_client_info.set_org_context (CONTEXT => '779'); -- org_idEND; |
Oracle r12 equivalent
1
2
3
| BEGIN mo_global.set_policy_context (p_access_mode => 'S', p_org_id => '779');END; |
OR
1
2
3
| BEGIN DBMS_APPLICATION_INFO.set_client_info (client_info => '779'); -- org_idEND; |
Tables that contain Multiple Organizations data can be identified by the suffix “_ALL” in the table name. These tables include a column called ORG_ID, which partitions Multiple Organizations data by organization.
Every Multiple Organizations table has a corresponding view that partitions the table’s data by operating unit. Multiple Organizations views partition data by including a DECODE on the internal variable CLIENT_INFO. This variable is set by the security system to the operating unit designated for the responsibility. It operates is a similar way to the LANGUAGE variable, which returns the language of the current session.
Note: If accessing data from a Multiple Organizations partitioned object when CLIENT.INFO has not been set (for example, from SQL*Plus), you must use the _ALL table, not the view.
SO_HEADERS_ALL, with its corresponding view SO_HEADERS, is an example of a Multiple Organizations partitioned object.
Multi Language context
In Oracle E-Business, the tables that end with _TL or views that end with _VL are multi language enabled. By default the language that is set is US English denoted as US within the tables. These tables have an entry for each language.
E.g. if you query for the concurrent program, Analyze All Index Column Statistics, you will find as many entries in the table,fnd_concurrent_programs_tl, as there are languages installed in the instance.
1
2
3
| SELECT * FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name = 'Analyze All Index Column Statistics' |
You can see there are 6 additional languages installed in this Oracle instance and hence there are 7 records for a single program on the table.
When MLS/NLS is installed the views affected by language do not show data. We would either need to query multi org setup views or set the language for the session.
We are trying to query an Oracle seeded view in the database
1
2
| SELECT * FROM hrfv_business_groups |
When we run the query we do not get any data
If you check the definition of the view,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| CREATE OR REPLACE FORCE VIEW apps.hrfv_business_groups (business_group_name, date_from,............ FROM hr_all_organization_units_tl ot, hr_all_organization_units o, fnd_id_flex_structures_tl f1, fnd_id_flex_structures_tl f2, fnd_id_flex_structures_tl f3, fnd_id_flex_structures_tl f4, fnd_id_flex_structures_tl f5, fnd_id_flex_structures_tl f6, hr_organization_information o2, hr_organization_information o3, hr_organization_information o4 WHERE o.organization_id = ot.organization_id AND ot.LANGUAGE = USERENV ('LANG')........ AND f1.LANGUAGE = USERENV ('LANG')........ AND f3.LANGUAGE = USERENV ('LANG')........ AND f6.LANGUAGE = USERENV ('LANG') WITH READ ONLY; |
I have only displayed the relevant lines from the view.
Now If I check the default language set on the Oracle session using,
1
2
| SELECT USERENV ('LANGUAGE') "Language" FROM DUAL; |
We shall now change the session language using
1
| ALTER SESSION SET nls_language='AMERICAN' |
After executing that you will get the message 
.
.
Now let us check the session language
Note that the language has been changed to AMERICAN
Now let us query the view once more
1
| SELECT * FROM hrfv_business_groups |
Now the view returns data as the language context has been set.
0 comments