RE: Confusing update issue!
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <EVAD@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Sep 2004 10:30:21 +0200
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Denham Eva
Sent: Monday, September 06, 2004 09:26
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //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@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts: