Re: How to Alter DUAL table to change value

Thanks for the humor in some of the posts guys, about looking for a new job :)

Unfortunately, I saw some of these replies a little late, but from another news group, I ended up doing the following....

Dropping the sequence, and recreating it.

With the recreation, I started the sequence with thenumber that I needed.

Was that too simplistic....??

Thanks again

Vince wrote:
> EdStevens wrote:
> > BookerT wrote:
> > > I am a novice user, but expected to do "Advanced things"
> > >
> > > I have inherited some asp/vb code that queries and modifies an oracle
> > > database. Some of the code in the database builds a Select Statement
> > > from the DUAL table to generate the primary key sequence number for a
> > > few tables (maybe just one, I have not determined the full scope yet).
> > > One of my tables that I keep trying to add a record to through the asp
> > > code keeps giving me a unique constraint error.
> > >
> > > This happened, I believe because we had to reimport the database from
> > > an earlier copy and thus numbers in the old tables reverted back to
> > > lower numbers.
> > >
> > > So for example, since new records have been added, my primary key id
> > > number for one table is as high as 27348.
> > >
> > > however when the code generates a number for the primary key id number,
> > > it has a number like 27257. I need to change the DUAL table entry, so
> > > that it starts at 27349 and that way when the code goes in and makes a
> > > connection to the database, it will pick up 27349 and then sequence
> > > from there.
> > >
> > > How do I do that?
> > >
> > > Currently in the code it goes:
> > > SELECT Inquiries_seq.NextVal AS NextID
> > > FROM DUAL
> > >
> > >
> > >
> > > Thanks for any opinions.
> >
> > DUAL is a 'dummy' table that simply provides a target for the FROM
> > clause. You keys are not coming from the DUAL table, they are coming
> > from an object called a 'sequence'. In your case the name of the
> > sequence is 'Inquiries_seq'. To reset the 'nextval' of the sequence
> > 'Inquiries_seq', you will have to drop and recreate it.
> >
> > The rest is left as an exercise for the student.
>
> Alternative to dropping and recreating sequences (and upsetting any
> dependancies based on them), you could just spin the sequence to the
> right number:
>
> DECLARE
>
> seqvalue NUMBER;
> tcount NUMBER;
>
> BEGIN
>
> SELECT MAX(primary_key_column)
> INTO tcount
> FROM mytable;
>
> SELECT my_sequence.NEXTVAL
> INTO seqvalue
> FROM dual;
>
> WHILE seqvalue < tcount LOOP
>
> EXECUTE IMMEDIATE 'select my_sequence.nextval from dual' INTO
> seqvalue;
>
> END LOOP;
>
> END;
>
> If your supporting sequences are named in such a way that they can be
> derived from their corresponding table names, you could even automate
> doing all of them within a similar procedure.
Received on Fri Dec 15 2006 - 05:58:06 CST