![\"Days](\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2011\/06\/DaysOfWeek.jpg\" "\\"Days")
<\/p>\n<\/p>\n
Business Objects (BO) is used to generate a report showing the schedule of employees. In the schedule the day of week is displayed as a header, which makes it easy for the employees to read. The data is retrieved from an Oracle database server.<\/p>\n
There are two situations, one with a native Oracle installation and one with a sequenced Oracle installation.<\/p>\n
In one situation the week starts on Sunday (zondag) in the other on Monday (maandag), resulting in a vague schedule for the employee. Although the date is correct, the corresponding day of week isn\u2019t.<\/p>\n
<\/p>\n
The day of week (DOW) is determined using the TO_CHAR function and the \u2018D\u2019 parameter. This results in a numerical result, which represents the day of week. In the BO report a translation from the numerical value to a dutch day of week has been made. <\/p>\n
CASE TO_CHAR(MA.STRT,'D')\n WHEN '1' THEN 'Zondag'\n WHEN '2' THEN 'Maandag'\n WHEN '3' THEN 'Dinsdag'\n WHEN '4' THEN 'Woensdag'\n WHEN '5' THEN 'Donderdag'\n WHEN '6' THEN 'Vrijdag'\n WHEN '7' THEN 'Zaterdag'\n ELSE ''\n END "Dag<\/pre>\nIn order to \u2018solve\u2019 the problem with the wrong day of week, the report operator changed the query. Although this results in the correct day of week, it isn\u2019t consistent on all environments. <\/p>\n
CASE TO_CHAR(MA.STRT,'D')\n WHEN '7' THEN 'Zondag'\n WHEN '1' THEN 'Maandag'\n WHEN '2' THEN 'Dinsdag'\n WHEN '3' THEN 'Woensdag'\n WHEN '4' THEN 'Donderdag'\n WHEN '5' THEN 'Vrijdag'\n WHEN '6' THEN 'Zaterdag'\n ELSE ''\n END "Dag"<\/pre>\n <\/p>\n
Regional settings<\/h4>\n
The number returned by to_char(some_date,\u2019D\u2019) varies with NLS_TERRITORY. The behaviour of to_char and to_nchar can be influenced by setting the NLS_DATE_LANGUAGE, NLS_CALENDAR, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY and NLS_ISO_CURRENCY NLS parameters. <\/p>\n
The NLS_LANG is set per machine in the registry on the location HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\KEY_OraClient10g_home1\\NLS_LANG.<\/p>\n
<\/p>\n
Configuration differences<\/h4>\n
![\"\"](\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2011\/06\/image_thumb.png\" "\\"\\"")
<\/a>The Oracle installation in the sequence (containing all other applications) is configured with the \u2018AMERICAN_AMERICA\u201d language.<\/p>\n <\/p>\n
<\/p>\n
![\"\"](\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2011\/06\/image_thumb1.png\" "\\"\\"")
<\/a><\/p>\nThe NLS_LANG on the native installed Oracle client is \u2018DUTCH_THE NETHERLANDS\u2019.<\/p>\n
<\/p>\n
<\/p>\n
According to the Oracle \u00ae Database Client Installation Guide<\/a>, the locale setting of your operating system determines the value of the NLS_LANG parameter at installation.<\/p>\n <\/p>\n
Solution<\/h4>\n
So, the \u201cproblem\u201d is caused by an inconsistent NLS_LANG and a Business Object report that assumes a certain syntax. The problem can be solved by setting the NLS_LANG in the sequenced version and the native installation to the same language code. <\/p>\n
An alternative could be to hardcode the NLS in the SQL function. The third parameter in the TO_CHAR function is the nls_date_language<\/em>. This way the results is consistent, no matter what NLS_LANG is set on the client. The parameters are described in the Oracle \u00ae Database Globalization Support Guide<\/a><\/p>\n <\/p>\n
Ingmar Verheij<\/p>","protected":false},"excerpt":{"rendered":"
Business Objects (BO) is used to generate a report showing the schedule of employees. In the schedule the day of week is displayed as a header, which makes it easy for the employees to read. The data is retrieved from an Oracle database server. There are two situations, one with a native Oracle installation and […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[294],"tags":[335,337,336],"class_list":["post-2804","post","type-post","status-publish","format-standard","hentry","category-microsoft-app-v","tag-businessobjects","tag-dow","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/2804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/comments?post=2804"}],"version-history":[{"count":0,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/2804\/revisions"}],"wp:attachment":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/media?parent=2804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/categories?post=2804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/tags?post=2804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2011\/06\/image.png\" "\\"\\"")
<\/a>The Oracle installation in the sequence (containing all other applications) is configured with the \u2018AMERICAN_AMERICA\u201d language.<\/p>\n