Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Perl and Java and PL/SQL and SYSDATE

Re: Perl and Java and PL/SQL and SYSDATE

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 03 Dec 2004 15:42:16 GMT
Message-ID: <sP%rd.37738$6q2.13185@newssvr14.news.prodigy.com>


Craig & Co. wrote:
> We converted the sysdate to CHAR back to DATE to enforce the
> NLS_DATE_FORMAT.
>
> Here is the - show parameter nls - output
> NAME TYPE VALUE
> ------------------------------------ ------- -------------------------------
> --

[...]
> nls_time_tz_format string HH24:MI:SSXFF TZH:TZM
> nls_timestamp_format string DD-MON-YYYY.HH24:MI:SSXFF
> nls_timestamp_tz_format string DD-MON-YYYY.HH24:MI:SSXFF
> TZH:TZM
>
> Originally we only had SYSDATE in the code, but the problem was occuring.
> So we tried to enforce the NLS_DATE_FORMAT.
>
>
> Select statement originally had 'order by' clause. I just didn't copy that
> line into the email.
> Message ID generated by a sequence number.
>
> Cannot reproduce problem at will - very random in it's picking and choosing
> of the 12 hour time difference.
> Same piece of code called by other java stored procedures and it works fine.
>
> > [...]
> Note: The machine and database are running in UTC.

You stated your version was 8.1.7.4, yet you show NLS parameters that include timezone components, I'm pretty sure this wasn't available until 9i. Even for 9i, you are showing a number of non-default settings, so you might want to find out who and why the settings were changed.

You should take an accurate inventory of every client that is accessing your database - are they all the same version? Are they all supported?   Do they all use the same NLS settings for their sessions?

As others pointed out, you should simply insert SYSDATE or TRUNC(SYSDATE) values in your database, forget about the conversion to char and back.

Note: all Unix machines "run" in UTC, they simply display time to the user based on a TZ setting.

-Mark Bole Received on Fri Dec 03 2004 - 09:42:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US