Perl and Java and PL/SQL and SYSDATE

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Thu, 2 Dec 2004 10:30:04 +1100
Message-ID: <41ae5448$0$59181$c30e37c6@ken-reader.news.telstra.net>

Hi,
Oracle 8.1.7.4
Solaris 8

We have a perl program that calls a Java Stored Procedure, which then calls a PL/SQL procedure
to insert data (including SYSDATE) into a table.

The code in the PL/SQL package is
INSERT INTO MESSAGE_DETAILS (
MESSAGE_ID, DOCUMENT_TYPE_CODE,
CURRENT_MESSAGE_STATUS, ACTIVE,
DOCUMENT_ID, IN_OUT, WHO, TRANSPORT_TYPE_CODE, DATA, SENT_RECEIVED_DTG,
SUBJECT, SENT_RECEIVED_DATE)
VALUES (
msgId, docTypeCode,
msgStatusCode, active,
documentId, inOut, who, transTypeCode, DATA, TO_DATE(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
subj, TO_DATE(TO_CHAR(SYSDATE, 'DD-MON-YYYY')));

The problem is every now and then the SYSDATE is 12 hours previous, i.e. the following select statement
produces the results below it.
select message_id, sent_received_dtg, sent_received_date from message_details
where sent_received_date = '01-DEC-2004'; MESSAGE_ID SENT_RECEIVED_DTG SENT_RECEIVED_DATE

---------- -------------------- --------------------
    514679 01-DEC-2004.22:06:34 01-DEC-2004.00:00:00
    514680 01-DEC-2004.22:08:05 01-DEC-2004.00:00:00
    514681 01-DEC-2004.22:10:31 01-DEC-2004.00:00:00
    514682 01-DEC-2004.10:10:42 01-DEC-2004.00:00:00
    514683 01-DEC-2004.10:10:42 01-DEC-2004.00:00:00
    514684 01-DEC-2004.22:13:32 01-DEC-2004.00:00:00
    514685 01-DEC-2004.22:16:04 01-DEC-2004.00:00:00

    514686 01-DEC-2004.22:16:54 01-DEC-2004.00:00:00     514687 01-DEC-2004.22:18:55 01-DEC-2004.00:00:00 As you can see above there at 10am times in the middle of 22 hour times. With the message_id in sequential order.

The java code that calls the stored package does so this way CallableStatement cstmt2 =
this.conn.prepareCall ("{call MESSAGINGSTORE.addRecipient(?,?,?,?,?)}"); // DECLARE that THE FIRST ? IS a RETURN VALUE OF TYPE LONG

cstmt2.setDouble (1, documentId);
cstmt2.setString (2, status);
cstmt2.setString (3, who);
cstmt2.setString (4, transport);
cstmt2.setString (5, actualNbr);
cstmt2.EXECUTE();
cstmt2.CLOSE();

The MESSAGINGSTORE.addRecipient packaged procedure then calls the MESSAGINGSTORE.createMessageDetails
with the following call
MESSAGINGSTORE.createMessageDetails(documentId, msgStatus, outgoing, who, transport, DATA);

The MESSAGINGSTORE.createMessageDetails packaged procedure has the following header.
PROCEDURE createMessageDetails(documentId IN DOCUMENT.DOCUMENT_ID%TYPE, msgStatus IN MESSAGE_STATUS_TYPE.DESCRIPTION%TYPE, inOut IN MESSAGE_DETAILS.IN_OUT%TYPE,
who IN MESSAGE_DETAILS.WHO%TYPE,
transport IN TRANSPORT_TYPE_CODE.DESCRIPTION%TYPE, DATA IN MESSAGE_DETAILS.DATA%TYPE) In this procedure the insert statement is executed as at the top of this message.

Any ideas, why we're getting the 12 hour difference.

Note: The machine and database are running in UTC.

Cheers
Craig. Received on Wed Dec 01 2004 - 17:30:04 CST