Re: How to Alter DUAL table to change value

On Dec 15, 6:58 am, "BookerT" <c..._at_ascac.org> wrote:
> 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.

Perfect! Exactly right. Received on Fri Dec 15 2006 - 10:53:30 CST