Re: Alter session on NLS_DATE_FORMAT being ignored
Date: Fri, 25 Feb 2011 10:11:14 -0800 (PST)
Message-ID: <fda5be86-4403-4ca4-943e-2c29600b99cc_at_18g2000prd.googlegroups.com>
On Feb 25, 6:06 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Feb 24, 9:05 am, Danmath <danmat..._at_gmail.com> wrote:
>
>
>
> > I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch
> > process right after the connection takes place. The format I specify
> > is YYYYMMDDHH24MISS, this is the format used all over the process. In
> > my development environment this works perfectly, but I've had problems
> > in other environments.
>
> > CASE A development environment: The process works fine, $NLS_LANG and
> > $NLS_DATE_FORMAT environment variables are not set.
>
> > CASE B Test envirnonment 1: The process failed.
> > $NLS_LANG=American_America.WE8ISO8859P1
> > $NLS_DATE_FORMAT environment variable is not set.
> > For some reason the $NLS_LANG variable seems to have more weight
> > than the alter session command.. why?
> > The process works fine after setting $NLS_DATE_FORMAT to the
> > desired format.
>
> > CASE C Test envirnonment 2: The process failed. $NLS_LANG and
> > $NLS_DATE_FORMAT environment variables are not set. Can't get it to
> > work here. Any idea why?
>
> My question to you is why are your relying on the nls_date format
> parameters at all? Every SQL statement that retrieves or passes in a
> date value should be written using to_char or to_date with the diresed/
> expected format mask specified. Then your code is environment
> independent. After all can your guarantee that your code will always
> run in an enfironment with the nls_date set the way you expect.
>
> IMHO -- Mark D Powell --
But that's the point - he's setting it to what he wants at the beginning of the session, a documented way to do it. The question is why wouldn't such a thing be sticky? We need more info for that. My guess is he isn't doing what he thinks he's doing. It could be as simple as a typo in the alter statement. It could be his access method has different sessions. It could be something else.
Of course, things like Bug 3944226 in 9.2.0.5/6 are worked around by
always being explicit about format in the SQL... some other alter
session silly possibilities noted in
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=241047.1#aref3
It is important to note also there are version and platform differences in how NLS parameters are handled. Windows can be especially frustrating, system environment variables ruling over the registry, and installation differences.
jg
-- _at_home.com is bogus. http://www.zdnet.com/blog/btl/sap-oracle-damages-grossly-excessive-oracle-wants-more-dough-if-new-trial/45328Received on Fri Feb 25 2011 - 12:11:14 CST