Re: help with date format !!!! urgent

On Jun 14, 4:55 pm, News <Contact_..._at_hotmail.com> wrote:
> On 13 juin, 23:48, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
>
>
>
>
> > On Jun 13, 5:19 pm, News <Contact_..._at_hotmail.com> wrote:
>
> > > On 13 juin, 00:50, sybra..._at_hccnet.nl wrote:
>
> > > > On Tue, 12 Jun 2007 12:12:06 -0700, News <Contact_..._at_hotmail.com>
> > > > wrote:
>
> > > > >On 12 juin, 20:44, sybra..._at_hccnet.nl wrote:
> > > > >> On Tue, 12 Jun 2007 09:47:04 -0700, News <Contact_..._at_hotmail.com>
> > > > >> wrote:
>
> > > > >> >On 12 juin, 17:20, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > > > >> >> On Jun 12, 3:48 am, News <Contact_..._at_hotmail.com> wrote:
>
> > > > >> >> The only sure way to fix this is to recreate your tables with proper
> > > > >> >> DEFAULT declarations:
>
> > > > >> >> CREATE TABLE HR.T (D DATE DEFAULT to_date('0001-01-01','YYYY-MM-
> > > > >> >> DD') NULL);
>
> > > > >> >Thanks ! but ..
> > > > >> >it's not my database !! I'm trying to clone Oracle Applications HR
> > > > >> >module database.
>
> > > > >> >The Tables are created this way by Oracle !!!!!!
>
> > > > >> >and the problem remains : why altering nls-date-format works in
> > > > >> >session level and not in system level in ini.ora ?
>
> > > > >> Which problem? Something which is working as designed?
>
> > > > >you mean nls-date-format in ini.ora is designed so .. to have no
> > > > >effect ?
>
> > > > I know you can't be bothered with reading manuals. If you would have
> > > > done so, this entire thread would have been redundant.
> > > > However, nls_date_format in init<sid>.ora (which you should stop using
> > > > in 9i and higher, and replace by a spfile) applies to code (stored
> > > > procedures) running on the server.
> > > > This setting is *always* overridden by the session NLS_DATE_FORMAT on
> > > > the client. As different clients can use different settings you
> > > > shouldn't rely on a default. You should always specify a format mask.
> > > > And IIRC NLS_DATE_FORMAT can be set as an env var prior to imp.
>
> > > I dont know in which manuals you picked up this. This is what all I
> > > found
>
> > > NLS_DATE_FORMAT specifies the default date format to use with the
> > > TO_CHAR and TO_DATE functions. The default value of this parameter is
> > > determined by NLS_TERRITORY.
>
> > > The value of this parameter can be any valid date format mask, and the
> > > value must be surrounded by double quotation marks. For example:
>
> > > NLS_DATE_FORMAT = "MM/DD/YYYY"- Hide quoted text -
>
> > > - Show quoted text -
>
> > Typically my scripts to invoke the import utility look like this
>
> > #!/bin/ksh
> > . /please/dot/in_some_file_to_set_environment_for_oracle
> > export NLS_LANG='some value'
>
> > /* example export NLS_LAND='AMERICAN_AMERICA.WE8ISO8859P1' */
>
> > Then you code the imp executable invocation.
>
> > The NLS_LANG will set the language and territory stuff which will then
> > cascade down to default date formats etc- Masquer le texte des messages précédents -
>
> You mean NLS_DATE_FORMAT depends on unix NLS_LANG env variable ?
>
> I thought about it but not explored it because i thought it would be
> silly design not documented anywhere
> one can have an operating system working in one language and an
> applicatin that works in another language.

Not documented anywhere? Not exactly.

Try doing a google search for NLS_LANG ...

Looks like first hit we get is this one ...

http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm

Hmmm gee whiz it's from oracle and talks about globalization.

Wonder if they mention anything about how it impacts client connections like the one the import utility uses?

Perhaps you might actually want to read this one along with my suggestion about setting it before running the import utility. That's the way most of us use import and export.

Of course you do appear to be having fun by setting an after logon database trigger.

Not to be mean but you have received some varied opinions here in response to your original question. Some of the responses are better than others. This is one that you might want to do some legwork on. Received on Thu Jun 14 2007 - 16:21:53 CDT