| Sequence in UPDATE [message #370925] |
Tue, 07 March 2000 22:42  |
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  |
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 |
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... :-)
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
