ole seem to ignore client nls settings - OO4O and Wizards(Archived)

Dear experts,
my dataserver has nls_parameters for american.
my webserver has nls_parameters set for protuguese.
alas my queries keep returning my dates is american format (MM/DD/YYYY) and not the brazilian DD/MM/YYYY.
my sql*plus works fine (that is it adapts its dates to my regestry settings)
what could be the problem?
Even when I first do a executeSQL("alter session set nls_date_format = 'DD/MM/YYYY'") and the execute my wuery, asp still shows me the american format...
If anyone could help me out... 

No, the NLS settings are geting used at the OCI layer, but then the Windows COM layer forces the regional settings on top of that.
Change the Windows regional settings for date/time.
Remember that with OO4O you have the following layers:
Your App-->COM-->OO4O-->OCI-->Net-->RDBMS

you could also possibly bind it as a string and then use a TO_CHAR() in your SQl statement to force a given format if you want to do it purely in your code.



I have a problem with my NLS settings.
Where can change the NLS_DATE_LANGUAGE globally on my PC, in TOAD i made the changes in NLS parameters, however when I run my JSP application, due to NLS_DATE_LANGUAGE, it's whowing in wrong out put format for my SQL queries.
See this thread for reference
SQL Doubt 
You can set it as environment variable : Control Panel -> System -> Advanced -> Environment variables -> System variables 
JSP uses JDBC and JDBC does not read environment variables (at least not JDBC Thin). In JDBC Thin, you should use explicit third parameter in the TO_CHAR calls in your code, or you could send an ALTER SESSION SET NLS_... command.
-- Sergiusz 
OK. but everytime for JDBC I have alter the session? It was working fine till I reformatted my PC.
Is there any settings in Jdeveloper I can set the NLS?
Have you try with embedded the NLS... into the to_char transformation, as I suggested Re: SQL Doubt ?<br>
Yes, that's how it' s working now.
But I would like to know how to get rid of embedding that in my SQL as it was working before.
JDBC will initialize NLS_LANGUAGE and NLS_TERRITORY to the language and country of the Java default locale.
Java will initialize its default locale to the O/S session locale.
This is how it works by default. You may change the default Java locale before connecting to Oracle, but this is recommended only, if this change is connected to an end-user's preference setting.
In your application:
1. Dates displayed on the screen should be in the format expected by end users. In simpler case, this may be just the default format as described above. In more advanced design, the format could be configurable.
2. To make them work well in any session locale, all constant dates in your SQL WHERE clauses and PL/SQL conditional expressions should be expressed using ANSI DATE or TIMESTAMP literals. See: "Oracle9i SQL Reference, Release 2 (9.2)", chapter 2 "Basic Elements of Oracle SQL", section "Datatypes", subsection "Oracle Built-in Datatypes", topics "DATE Datatype" and "TIMESTAMP Datatype". (It is described in a separate topic "Datetime Literals" in 10.2 docs.)
If you do not want to use ANSI literals but rather text literals with TO_DATE, you should always specify the explicit date format with 4-digit year. The format should use only numerical elements and you should specify NLS_CALENDAR=GREGORIAN in the third parameter to make sure that the year is interpreted correctly (unless you want to express constants in another calendar).
-- Sergiusz 
Thanks a lot, it worked for JDBC. I changed the default Java locale in the control panel regional settings from arabic to english.

NLS_NUMERIC_CHARACTERS is incorrect for English/Canada settings

I've just installed XE on my XP sp2 machine and I can't find a way to get the session to recognize the default (and my) Regional & Language settings for English Canada. The settings are decimal point for a decimal, and comma for grouping, so NLS_NUMERIC_CHARACTERS should = '.,'.
Yet the session value for NLS_NUMERIC_CHARACTERS is a comma ',' and I have no idea where this is coming from.
Alter session appears to have no effect after running the alter session statement itself in SQL Commands:
'Statement processed.
0.40 seconds' (note decimal point is there)
Then immediately run another SQL statement e.g. 'SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;'
and the result:
4 000,00 (back to being a comma)
1 rows returned in 0,01 seconds (back to being a comma)
Now, I don't want to have to be setting session values anyway.
What could be causing XE (or Personal Edition for that matter - had the same problem with that...) to not recognize the values as defined in Control Panel -> Regional & Language Settings for English/Canada?
Note that NLS_LANG = ENGLISH_CANADA.WE8MSWIN1252. I did try changing NLS_LANG to AMERICAN_AMERICA.etc but that made no difference.
NLS_DATABASE_PARAMETERS and NLS_INSTANCE_PARAMETERS show American/America values (default for the installation?) NLS_SESSION_PARAMETERS shows English/Canada values which, other than NLS_NUMERIC_CHARACTERS, appear to be correct for my settings.
I've spent way too much time going through the docs, other semi-related forum entries and continually reconfiguring & rebooting my machine to no avail... Any help would really be appreciated!
What could be causing XE (or Personal Edition for that matter - had the same problem
with that...) to not recognize the values as defined in Control Panel -> Regional &
Language Settings for English/Canada?Oracle Installer (for XE and for higher editions) don't care for settings defined in Control Panel -> Regional & Language Settings.
Well, as wrote one member of Forum once: "only Oracle knows"... 
First, Oracle usually does not obey all the settings in Control Panel. Oracle tools that are Java-based use the language and country settings of the operating system to initialize Java default locale. Numeric characters should be then taken from Java's locale data. Oracle tools that are C-based and OCI-based usually obey the NLS_LANG setting and other NLS environment variables. They may obey DB-side session settings if they synchronize these settings with the client side. NLS_LANG is set by Oracle Installer based on the OS language and country but it may be later modified.
To comment on the particular problem in this thread, I need to know first what tool we are talking about. Oracle Database 10g XE is, as the name says, a database. Displaying of results happens in a tool: SQL*Plus, HTML DB, etc.
-- Sergiusz 
Thanks, Faust and Sergiusz, for your replies. There were actually 2 issues to deal with here and I've resolved them both (finally!)
My initial testing was only with the XE web tools accessed via 'apex' - running a query in 'SQL Commands', and viewing a table's data in 'Object Browser'. Nothing that I changed in my XP Regional Settings (comments above noted) or as a value for NLS_LANG made any difference here.
BUT, there is one more setting that does come into play in this situation and that is in Internet Explorer itself! - version 7 in my case. Of course I had my Tools -> Internet Options -> Languages set to English (Canada) [en-CA]. Changing this setting to English (United States) [en-US] solves the problem for numeric formats within the XE web tools. (CA and US settings SHOULD give the same result...)
Testing numeric results with SQL*Plus, Toad, and via ODBC (set to use Oracle NLS settings) showed that NLS_LANG = ENGLISH_CANADA.WE8MSWIN1252 doesn't get translated properly (at least not on my computer). AMERICAN_AMERICA.WE8MSWIN1252 does, of course. They SHOULD be exactly the same when it comes to NLS_NUMERIC_CHARACTERS.
Thanks again!
You are right that ENGLISH_CANADA should give you decimal period while FRENCH_CANADA should give a comma. This functionality was theoretically implemented in 10g and named locale variants. Unfortunately, the 10g implementation is broken and the old behavior of having one decimal character per territory is still there. Hopefully, it will work in the next database version.
-- Sergiusz

How to change Data Format

I'm using Sql Developer vers. 1.1.2. for Oracle 8.1.7 and I can't change Data Format. Even if first I run "alter session set nls_date_format = 'DD/MM/YYYY';" and after I run "select data_field from table;" in the SQL Worksheet, it goes on reporting the field in format "MM/DD/YYYY HH:MI:SS". It seems to me as if the "alter session" doesn't work.
I've got a problem with decimal separator too. I mean when I run "select salary_field from table;" and the item is for example 2300,32 the report gets null column even if I've set Decimal Separator to "," from Tools>NSL Parameters>Decimal Separator.
I need help.
Thanks in advance
It isn't really a great help, as you need to access 8.1.7, but the current version doesn't have this problem - it uses the current NLS_DATE_FORMAT setting to format the date, regardless of the NLS preferences.
I have vague memories (1.1.2 has largely faded from my memory) that when they first introduced the NLS preferences that they took precedence over the DB NLS settings (ie date formatted in SQL Developer based on NLS preferences).
On the decimal separator, I have other vague memories of problems if you didn't set both the Decimal and Group separator, although a quick search on the forum didn't highlight something that seemed the same as your problem. After logging on (ie with the NLS preferences as the DB NLS settings), what do you get if you the NLS_NUMERIC_CHARACTERS parameter in the NLS_SESSION_PARAMETERS view? This should have two characters - the first one being the decimal separator and the second being the group separator.

Hijrah Calendar

i want to change the database calendar to be Hijrah ? i dont know how to do it, there is a command for altering the session but i want to change the database?
thank you 
All calculations in the database are performed using the Gregorian calendar and all datetime values are stored in this calendar. Hijrah calendar is for textual presentation only. That is, it influences TO_CHAR, TO_DATE and the related timestamp conversion functions. You can specify NLS_CALENDAR for the session, using ALTER SESSION or the client environment setting (the latter except for JDBC Thin clients), or directly in TO_CHAR/TO_DATE/TO_TIMESTAMP calls, in the third argument to the functions. You can theoretically set NLS_CALENDAR in init.ora but this is not especially useful or recommended as the setting is usually overridden by the client.
If you need to set the setting centrally, an AFTER LOGON trigger is probably the best option.
-- Sergiusz

numeric format problems

got this problem 
I have this problem: I want to insert/edit data to an oracle database. Everything works well, but when I want to work with numeric data, I have to put comma. I would like to type a point instead.
Where can I set a configuration for this?
This is a function of your NLS settings-- if you're set to a language that uses the comma as the decimal point rather than the period (i.e. 1.000,00 vs 1,000.00). You'll have to change that setting.
Justin Cave 
Alter the NLS_NUMERIC_CHARACTERS to (.,) either at session or initfile level and try. You should be fine then.