Re: Perl and Java and PL/SQL and SYSDATE

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Thu, 2 Dec 2004 15:45:40 +1100
Message-ID: <41ae9e41$0$59192$c30e37c6@ken-reader.news.telstra.net>

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_calendar                          string
nls_comp                              string
nls_currency                          string
nls_date_format                       string DD-MON-YYYY.HH24:MI:SS
nls_date_language                     string
nls_dual_currency                     string
nls_iso_currency                      string
nls_language                          string AMERICAN
nls_numeric_characters                string
nls_sort                              string
nls_territory                         string AMERICA
nls_time_format                       string HH24:MI:SSXFF
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.

Table Structure
SQL> desc message_details

Name                                         Null?          Type


----------------------------------------- -------- -------------

MESSAGE_ID                                NOT NULL NUMBER(10)
DOCUMENT_TYPE_CODE                        NOT NULL VARCHAR2(7)
CURRENT_MESSAGE_STATUS                             VARCHAR2(7)
EXPECTED_MESSAGE_STATUS                            VARCHAR2(7)
ACTIVE                                             CHAR(1)
DOCUMENT_ID                                        NUMBER(10)
IN_OUT                                             CHAR(1)
WHO                                                VARCHAR2(255)
TRANSPORT_TYPE_CODE                                VARCHAR2(7)
DATA                                               VARCHAR2(200)
SENT_RECEIVED_DTG                                  DATE
MSG_SIZE                                           NUMBER(10)
ARCHIVED_DTG                                       DATE
IPMID                                              VARCHAR2(50)
SUBJECT                                            VARCHAR2(100)
SENT_RECEIVED_DATE                                 DATE

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.

Cheers
Craig. Received on Wed Dec 01 2004 - 22:45:40 CST