Before this support incident I never realized that browser language setting can "Wreak Havoc" for oracle queries.
We have supportable application developed in APEX which authenticates users with RSA login and check if necessary responsibility is available with user. The issue occurring was few users were getting access denied custom errors even when they had needed access.
After many days of debugging efforts we could get to root of Access Denied error users were receiving in Firefox browser.
Usual debugging steps like trying the queries at our end , logging debug messages were not giving expected results. The same user was able to login from different desktop giving suspicion on browser settings.
Usual helpdesk tips like uninstalling browser and re-installing it also didn't worked.
After some brainstorming on debug messages we came to conclusion that query itself is failing and getting changed run time for the user. On close look we found use of _VL tables from Oracle EBS. Then we asked user for browser language and alaas..... user was using en-gb( English United Kingdom) as browser language and this was causing count zero for the query checking active supportable responsibility.
Browser language change set nls language on database session changing native language and queries can fail if US English is not default language. Other altenative is not to use_VL or _TL views from Oracle EBS and to design queries on base tables directly.
Lesson learned after too many to and fro communications with user and constrained testing environment!!!
Technical details
Setting language runs below
export NLS_LANG="ENGLISH_UNITED KINGDOM.WE8MSWIN1252"
The purpose of the ALTER SESSION statement is "to specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database."
Query which caused failure
INTO l_user_resp_cnt
FROM fnd_user fu,
fnd_user_resp_groups_all furgd,
fnd_responsibility_vl frv
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
AND fu.user_name = APEX_CUSTOM_AUTH.GET_USERNAME
AND fu.user_name = v_final_user
AND frv.responsibility_name = 'RH Supportable Self Service'
AND furgd.start_date <= sysdate
AND NVL(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date <= sysdate
AND NVL(fu.end_date, sysdate + 1) > sysdate
AND frv.start_date <= sysdate
AND NVL(frv.end_date, sysdate + 1) > sysdate;
View FND_RESPONSIBILITY_VL uses t.LANGUAGE = USERENV ('LANG') condition which causes no data found and hence count 0
Fix was to use FND_RESPONSIBILITY_TL FND_RESPONSIBILITY tables in query
Good debugging stuff, when ideas are dried up.. right ? :) Keep working
We have supportable application developed in APEX which authenticates users with RSA login and check if necessary responsibility is available with user. The issue occurring was few users were getting access denied custom errors even when they had needed access.
After many days of debugging efforts we could get to root of Access Denied error users were receiving in Firefox browser.
Usual debugging steps like trying the queries at our end , logging debug messages were not giving expected results. The same user was able to login from different desktop giving suspicion on browser settings.
Usual helpdesk tips like uninstalling browser and re-installing it also didn't worked.
After some brainstorming on debug messages we came to conclusion that query itself is failing and getting changed run time for the user. On close look we found use of _VL tables from Oracle EBS. Then we asked user for browser language and alaas..... user was using en-gb( English United Kingdom) as browser language and this was causing count zero for the query checking active supportable responsibility.
Browser language change set nls language on database session changing native language and queries can fail if US English is not default language. Other altenative is not to use_VL or _TL views from Oracle EBS and to design queries on base tables directly.
Lesson learned after too many to and fro communications with user and constrained testing environment!!!
Technical details
Setting language runs below
export NLS_LANG="ENGLISH_UNITED KINGDOM.WE8MSWIN1252"
The purpose of the ALTER SESSION statement is "to specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database."
Query which caused failure
INTO l_user_resp_cnt
FROM fnd_user fu,
fnd_user_resp_groups_all furgd,
fnd_responsibility_vl frv
WHERE fu.user_id = furgd.user_id
AND furgd.responsibility_id = frv.responsibility_id
AND fu.user_name = APEX_CUSTOM_AUTH.GET_USERNAME
AND fu.user_name = v_final_user
AND frv.responsibility_name = 'RH Supportable Self Service'
AND furgd.start_date <= sysdate
AND NVL(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date <= sysdate
AND NVL(fu.end_date, sysdate + 1) > sysdate
AND frv.start_date <= sysdate
AND NVL(frv.end_date, sysdate + 1) > sysdate;
View FND_RESPONSIBILITY_VL uses t.LANGUAGE = USERENV ('LANG') condition which causes no data found and hence count 0
Fix was to use FND_RESPONSIBILITY_TL FND_RESPONSIBILITY tables in query
Good debugging stuff, when ideas are dried up.. right ? :) Keep working
