Home » RDBMS Server » Server Administration » Sequence in UPDATE
Sequence in UPDATE [message #370925] Tue, 07 March 2000 22:42 Go to next message
Ron Hodges
Messages: 2
Registered: March 2000
Junior Member
The following syntax works in Oracle 8.1.5 but not in 7.3.4, what am I missing?

UPDATE tablename
SET field1 = sequence.nextval,
field2 = 'value'
WHERE field3 = 'somevalue'

Do I have to select the sequence.nextval from DUAL and store it in a variable in 7.x?

Thanks!
Re: Sequence in UPDATE [message #370927 is a reply to message #370925] Wed, 08 March 2000 06:49 Go to previous messageGo to next message
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
Junior Member
Hello,

Good Day!

I have tried the same on Oracle Version 7.x and noticed that there are two reasons why it won't work...

STEP 1: Check the USER_SEQUENCE table ... because in the earlier versions of Oracle;at first when the Oracle Instance starts up by default we won't get ALTER SEQUENCE ... privileges....If you have the appropriate privileges then shutdown the session and restart it again.

STEP 2: In ur DML Statement ...try to use LTRIM & RTRIM to only char/varchar type fields.

For Ex: UPDATE tablename SET field1 = sequence.nextval,field2 = 'value'
WHERE LTRIM(RTRIM(field3)) = 'somevalue'

Hope so it works......

Regards

Atavur Rahaman S.A
Re: Sequence in UPDATE [message #370928 is a reply to message #370927] Wed, 08 March 2000 09:10 Go to previous message
Ron Hodges
Messages: 2
Registered: March 2000
Junior Member
Thanks for your suggestion! It never would have occurred to me to check that, because the user ID was used to create and drop sequences as part of a data migration script and that worked fine. As it turned out, that ID did not explicitly have ALTER SEQUENCE privileges. Those are being added now, so we shall see... :-)
Previous Topic: ORACLE_SID
Next Topic: procedure inside in-house program
Goto Forum:
  


Current Time: Tue Oct 20 09:31:47 CDT 2020