Re: NLS_DATABASE_PARAMETERS vs. v$parameter

From: Leo Van Nieuwenhuyse <leo.van.nieuwenhuyse_at_pandora.be>
Date: Thu, 23 Dec 1999 22:56:15 +0100
Message-ID: <pSw84.1961$78.7664_at_afrodite.telenet-ops.be>


Steve, first of all you must know about the 3 levels of the nls_parameters :

  1. The database value, as set in the INIT.ORA file
  2. The client value, on windows the value in the \hkey\local_machine\software\oracle ,on unix the environment variables
  3. The session value, after issuing an alter_session or DBMS_SESSION.

The first is always default and can be overruled by the second and the third.

NLS_LANG contains the language, territory and character the language part of it can be overruled by NLS_LANGUAGE and the territory part by NLS_TERRITORY

NLS_LANGUAGE specifies the default conventions for the following session characteristics:
language for server messages
language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)
symbols for equivalents of AM, PM, AD, and BC default sorting sequence for character data when ORDER BY is specified (GROUP BY uses a binary sort, unless ORDER BY is specified) writing direction
affirmative/negative response strings

NLS_TERRITORY specifies the conventions for the following default date and numeric formatting characteristics:
date format
decimal character and group separator
local currency symbol
ISO currency symbol
week start day
credit and debit symbol
ISO week flag
list separator

The date format part of NLS_TERRITORY can be overruled by NLS_DATE_FORMAT

So in fact the whole thing is rather logical if you follow the different rules and priorities.
V$PARAMETER gives you the setting for your session.

Steve Chapman <schapman_at_mindspring.com> schreef in berichtnieuws 83roms$hkh$1_at_nntp5.atl.mindspring.net...
> Hi All,
>
> I believe that the following is true:
>
> (1). INIT.ORA settings are read up and fed into the SYS.V$PARAMETER view.
>
> (2). NLS_DATABASE_PARAMETERS view looks at SYS.PROPS$ table for its
values.
> CREATE OR REPLACE VIEW NLS_DATABASE_PARAMETERS ( PARAMETER,
> VALUE ) AS select name,
> substr(value$, 1, 30)
> from props$
> where name like 'NLS%'
>
> Q: Where does SYS.PROPS$ table get its values? Why aren't they the SAME
as
> the v$parameter values? Do we hack in new values into the SYS.PROPS$
table
> to make changes? What effect does CREATE DATABASE and/or ALTER DATABASE
> have on v$parameter and/or sys.props$?
>
> We have an Ora8i (8.1.5.0.0) on WinNT db, with NLS_DATE_FORMAT =
"MM/DD/YY"
> in the NLS_DATABASE_PARAMETERS view, but NLS_DATE_FORMAT = "MM/DD/YYYY" in
> the SYS.V$PARAMETER view. It's INIT.ORA file has "NLS_DATE_FORMAT =
> MM/DD/YYYY" as the last line of the file, and we restarted the database
many
> times since the change was made.
>
> When I log into SQL*Plus, execute "select sysdate from dual", I get
> "22-DEC-1999", hence it is using MM/DD/YYYY.
>
> I have read that the NLS_LANGUAGE and/or NLS_TERRITORY (American, German,
> etc.) overrides NLS_DATE_FORMAT settings, but I'm not quite sure how.
>
> I know the information from this web page source (thanks to the author):
>
> http://osi.oracle.com/~tkyte/Misc/NLSDateFormat.html
>
> So I'm confused why SQL*Plus would use MM/DD/YYYY from v$parameter instead
> of MM/DD/YY from NLS_DATABASE_PARAMETERS.
>
> Am I missing something?
>
> Thanks in advance!
>
> --
> Steve Chapman
> TOAD Development Team
> Quest Software, Inc.
>
> www.toadsoft.com
> www.quest.com
>
>
>
>
>
Received on Thu Dec 23 1999 - 22:56:15 CET

Original text of this message