RE: Confusing update issue!

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Mon, 6 Sep 2004 10:30:21 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNIECCCIAA.lex.de.haan@naturaljoin.nl>

Denham,

I think the best cure is to solve the root problem -- that is, the script itself.
try to make your SQL code independent of any client-side or database session parameters,
by specifying appropriate DATE values, rather than relying on "conversion on the fly".

you can do that in two ways:

using the TO_DATE function with an appropriate date format
using DATE literals, e.g. DATE '2004-09-08'

Kind regards,
Lex.

visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>

skype me <callto://lexdehaan>

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Denham Eva Sent: Monday, September 06, 2004 09:26
To: oracle-l_at_freelists.org
Subject: Confusing update issue!

Hello Group,

Need an explanation for the following occurrence.

We have a large script doing approximately 2000 updates.

Within this script there is a update of a date column with a date such as '8-SEP-2004'

i.e.

update pm set pm15 = 'Y',pm1 = 'EMTR-C',crewid = 'CREW06',supervisor =
'6113603',pm4 = 'A',pmcounter = '2',pm13 = 'MON',firstdate =
'8-NOV-2004',nextdate = '8-NOV-2004' where pmnum = 'L1050216';
update pm set pm15 = 'Y',pm1 = 'EMTR-C',crewid = 'CREW06',supervisor =
'6113603',pm4 = 'A',pmcounter = '2',pm13 = 'MON',firstdate =
'8-NOV-2004',nextdate = '8-NOV-2004' where pmnum = 'L1050217';
update pm set pm15 = 'Y',pm1 = 'EMTR-C',crewid = 'CREW06',supervisor =
'6113603',pm4 = 'A',pmcounter = '3',pm13 = 'MON',firstdate =
'11-OCT-2004',nextdate = '11-OCT-2004' where pmnum = 'L1052951';
update pm set pm15 = 'Y',pm1 = 'EMTR-C',crewid = 'CREW06',supervisor =
'6113603',pm4 = 'A',pmcounter = '2',pm13 = 'MON',firstdate =
'20-SEP-2004',nextdate = '20-SEP-2004' where pmnum = 'L1052952';
update pm set pm15 = 'Y',pm1 = 'EMTR-C',crewid = 'CREW06',supervisor =
'6113603',pm4 = 'A',pmcounter = '2',pm13 = 'MON',firstdate =
'20-SEP-2004',nextdate = '20-SEP-2004' where pmnum = 'L1052953';

Once this script has been run, column firstdate will be updated to an arbitrary date like 20-SEP-2004 instead of 8-NOV-2004. Dates of more than two characters seem to work OK, ie '11-SEP-2004'.

I have tried alter session and setting nvl_date_format = 'DD-MON-YYYY'. Same thing.

A single row update, as in highlight one row and then execute works fine.

Run this script in different front ends including toad. Same thing.

I have tried placing a commit after each line same issue.

I have studied the triggers and procedures for some clue nothing, there is no reversing code.

Has anyone experienced this before? Any explanations?

Is there something else I can try to debug this issue that I have not tried?

Many Thanks

Denham

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe
To search the archives - http://www.freelists.org/archives/oracle-l/


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/

Received on Mon Sep 06 2004 - 03:26:00 CDT