Re: How to Alter DUAL table to change value

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 Thu Dec 14 2006 - 18:57:39 CST